Swarup, Please notice the P.S. at the bottom before you continue. 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]
