Using Excel VBA to Insert New Records in Access

[RollinAgain]

Sorry about the double post but I didn't see the Access Module Forum until after I posted in the general MS Office Forum.

I have a very large Excel file that contains information about various Employee timesheets. I am using Excel 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 in Excel, 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 associated 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

 

[Crowley16]

why are you doing this in excel?

wouldn't it be much easier to import all the information into access, and then process it there?

 

[RollinAgain]

Why am I doing this in Excel VBA?

The source file (Excel) is automatically generated by another application and comes back to me with lots of other USELESS information, graphics, codes, dates, page headers, etc. The Excel file would require extensive manual formatting and cutting/pasting in order to make it an "import friendly" file for Access. In addition to this, the important data that I want to put into the database tables is randomly spread throughout the spreadsheet in no logical order or pattern. There is alot of logic built into my existing macro to evaluate certain ranges and cells in the spreadsheet and take the appropriate action based on their contents. By using VBA in Excel I am able to isolate only the data I need.

I am able to update the main Summary table just fine with my code. Lets say I insert a new record into the main table and it automatically receives a new recordnumber of 115, how can I store this number (115) as a variable so that later I can insert this same number into a different table as one of the fields for each of the associated detail records.



Rollin

 

[PHV]

How are you inserting the rows in Access ?
If by OLE Automation, then you can consider the Access VBA DLookUp function to retrieve the AutoNumber value.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[PJStephenson]

if you use some code like this:

With rst
.AddNew
    .Fields(1).Value = "Test"
.Update
    varNewRecord = .Fields(0).Value
End With

then you should get the autonumber returned. I do when using ADO to connect to a database.

HTH
Peter

 

[RollinAgain]

PJ,

Thanx for your response. That is exactly what I was looking for.


Rollin