On Wed, 2007-09-05 at 18:23 +0200, Andreas Saeger wrote: > Swarup, > Please notice the P.S. at the bottom before you continue.
I've got success. The database is created, it has all the correct data in it, and it is fully editable. Using the guidelines you've given in this current letter about how to set up the "Date" field in the spread sheet and get all the dates to copy over in the ISO format, it all worked. There is one slight problem I do have, which I can manage with but which should be able to be fixed. I tried having the wizard set up its own primary key. But after finishing all the field parameters etc in the wizard and giving it the order to "create table", it gave an error: "java.lang.NullPointerException". Any idea why? And having given that error, when I clicked on "ok", then that was the end. That is, it didn't even create the table. So I tried it again, this time without having the wizard create a primary key. And without a primary key, it created the whole table just fine. Then I went to the table's contextual "edit" and made my "ID" field the primary key. And it worked. The table opens and is fully editable. The only question that remains is that, the primary key does not auto-add when I make a new row at the bottom of the table. It should auto-generate a new number, but it is not doing that. I have to type it in myself. --But that is the only problem. So how do I make the primary key autogenerate a new number when I start a new row in the table? If I can understand how to do that, then this whole work will be complete. Many thanks for all your help, Swarup > Dinbandhu wrote: > > [...] > > Hi Andreas, > > [...] > > 1. I think I was able to understand and follow each step of your > > instructions. And in the end, the table was made. Indeed, all the > > columns and rows are there, correctly labeled. And all the data is there > > as well-- with two exceptions. I have two columns which are of date > > type. Those columns are both completely blank. I'm not certain why, but > > as I say, I suspect you will be able to tell me by seeing what I did, > > described here below. > > > [...] > > > So something went wrong in the production of that K column. > > In another post you mentioned that your dates where shown as #### in a > spreadsheet (you opened a csv text table). This indicates that the > column is too narrow to display the cell's numeric value (spreadsheet > dates are numbers). If this hapens to you, simply drag the column wider. > The values are there anyway. > By the way: The exact names of first row's headers are not of > importance. They are just labels and each column should have a unique > one. The cell addresses I mentioned (e.g. I97) refer to the imported > data from *my* test database. Your columns' addresses (A,B,C,...) and > your last row might be different, so my example's cell address I97 was > the last bottom-right cell in *my* table. > > "Date", "Time" and "Time Stamp" are three different types, even if the > Access driver may deliver dates with a 00:00:00 time appended. In my > example (having the date/time labeled "GMT") I converted the (numeric) > spreadsheet values to ISO string by formula > =TEXT(Some_Cell_in_this_row;"YYYY-MM-DD HH:MM:SS"). In the import > wizzard I declared that field as time stamp and the values where > imported as such. If you just want the plain dates, omit the > hours:minutes:seconds =TEXT(Some_Cell_in_this_row;"YYYY-MM-DD") > > Your actual dates may start in cell B2 (below some label in B1) and go > down until -let's say- B1234. If your table contains 5 columns (A to E), > your first unused column is column F, where you enter into F2 > =TEXT(B2;"YYYY-MM-DD") which should yield the ISO representation of the > same date in B2. Drag down F2 until the end F1234, so each value in F > shows the ISO representation of the same date in B. > Just to be shure: Check if the (first few rows of) spreadsheet values > are identical to the ones in you Access table, visible in the datasource > window. > Check if the converted dates in the last column are identical to the > original ones (same year, month, day). > Avoid blank columns and rows. Each copied column of cells should contain > one label in first row, some values below and should not exceed the end > of the list. Of course you can move unwanted columns aside and exclude > them from your selection before copying. Skipping those columns later in > the wizzard (as I suggested before) is just another option. > > > I should just mention that I repeated this whole process for the second > > date colomn, and called the original "SKIP2". > > > Yes, likewise. > > > The other thing I want to confirm with you is that, in the copy table > > wizard where for each field you have to specify data types, for each of > > the two date columns I created in the spreadsheet as ISO strings, I > > specified those columns as of type "timestamp". Was that the correct > > field type? As far as I understood, this was correct rather than "date", > > since the MS Access field contains both date and time. > > > See above. If you mean dates without times, convert to plain date and > import as plain date. > > > 2. The second place where the results deviated from what was expected, > > is where you wrote: > > > > "When you finish the wizard, you'll probably get some error. Don't > > bother, continue. Your table will be blank, since there is no primary > > key yet. Choose "Edit" from the new tables context menu and "Primary > > Key" from the context menu of your ID field." > > > > First, although there was no primary key yet, still I did not get any > > error. (It was exciting for me not to get it actually, as this was the > > first time in all my attempts to do this wizard, where I didn't get an > > error at this stage.) > > > > The table was created, and it opens just fine. But I could not find > > where you indicated "Choose "Edit" from the new tables context menu and > > "Primary Key" from the context menu of your ID field." When the table is > > opened, under the "edit" drop-down menu there is no option for primary > > key. And right-clicking on the ID field does not give any option for > > primary key designation, either. > > > Switch to the database document (odb) which contains your target > database (not the one connecting to your Access database). You'll find > the newly created table. Select it (don't open) and call "Edit ..." from > it's context menu. You get a table-design view, similar to the one in > Access, where you can edit some (unfortunately not many) properties of > the table's fields. Right-click the grey square at your ID field and set > the primary key. Save the table and it should be editable. > > > 3. The last thing is this table that is created, still seems to be > > "read-only". All the various options for editing and managing the table, > > are grayed out. But perhaps this is due to the above described problem > > #2, that there is no primary key. > > > You are right. No write access without primary key. > > > Thanks for all your help, > > Swarup > > > > P.S. > OK, having written all that, I see another problem. Did you use the ID > field as an auto-field in Access? Are it's values generated > automatically when you enter new records in Access? And then: Is that ID > used as a foreign key in other tables (do other tables' fields relate to > this field)? > This implies that we have to reuse the exact old values in both tables. > When you set an already existing field to be automatically incremented, > the field is newly generated and nothing can guarantee that it's values > are the same as before. > If the above assumptions are true, let the import wizzard create a new > primary key (there is an option at step one of the wizzard). Import the > old (Access) primary key as a plain field of integers. With the new > primary key the table will be writable instantly. > Later we can synchronize the values in the dependent table with the new > primary key so all relations will refer to the right thing in the main > table. > > --------------------------------------------------------------------- > To unsubscribe, e-mail: [EMAIL PROTECTED] > For additional commands, e-mail: [EMAIL PROTECTED] > --------------------------------------------------------------------- To unsubscribe, e-mail: [EMAIL PROTECTED] For additional commands, e-mail: [EMAIL PROTECTED]
