Access VBA - Inserting New Records

[RollinAgain]

I have a very large Excel file that contains information about various Employee timesheets. I am wanting to use VBA to insert the information into several Access Tables.

Here is an example of how each of the Excel Record would appear:


NAME WEEK ENDING TOTAL HOURS

John Doe 06/09/04 35

DATE HOURS

06/05/04 8
06/06/04 8
06/07/04 8
06/08/04 6
06/09/04 5


The Summarized information (Name, Week Ending, Total Hours) will go into a Table called "Summary." This table has 4 fields. The first field is called "Record Number" and is an autonumber field while other 3 fields are called "Name", "Week", and "Hours" My code works fine and the table is successfully updated with this all this info.

Here is an example of the first Summary Record created

1 John Doe 06/09/04 35


After this Summary table has been updated, I need to insert each of the individuals days information into another table called "Details" Using VBA, how do I tell Access to use the same record number as was assigned to the related main record created in the "Summary" table when creating each of the detail records ?


Here is what each of the associated Detailed Records should look like. The first field value (record number) should be the same as the record number in the main record above. How do I do this in VBA?

1 06/05/04 8
1 06/06/04 8
1 06/07/04 8
1 06/08/04 6
1 06/09/04 5




Thanks
Rollin

 

[afryer]

Hi,

I'm afraid I haven't got time to post a full solution here (at work at the mo), but I can give you some pointers on how to do it.

1) In VBA you need to create a database object referencing your Access database.
2) Once you do that you need to create a loop, which reads through each of the records in your table
3) Each record can be referenced by using the

worksheets ("myworksheet").rangge ("A" & l_row)

Obviously "A" would be changed for the different columns in the spreadsheet.

4) For each record your read in you will need to create a SQL statement:

l_str_sql = "INSERT INTO <my_table> VALUES " & <list of values>

5) Finally you need to execute the SQL statement.

l_database.execute l_str_sql

Incidentally a MUCH easier way to do it would be to save your sheets as .csv files and import them into access this way, but if you wan't to do it in VBA then that is how I would do it.

With regards to the unique indexes I would personally create an extra row in your spreadsheet containing the unique ID - you could work it out in the code if you liked, but it would be a bit of a pain. Let me know if you need to do that though.

Hope this helps

Andrew

 

[xlbo]

Pretty much the same as above but I would use an APPEND query for your summary data - just use the row reference to pick up just that line

Once that query has been run, you can use DLOOKUP (from within the Access reference) to obtain the record key for that employee. Set that = to a variable and use it when you INSERT the detail data into the detail table so

1) Set reference to Access in VBE
2) open db and set to variable
3) get summary data from excel - put in recordset
4) append recordset to summary table
5) Use DLOOKUP to obtain the recordnumber you have just created
6) Apply recor number to excel sheet (insert column or whatever)
7) Append these records to summary table

Look in the VBA help files on keywords APPEND & RECORDSET for more details

Rgds, Geoff

Never test the depth of water with both feet

Help us to help you by reading FAQ222-2244 before you ask a question