Importing Recordsets

[fernin8r]

Here's the problem.

There is a database that is saved on our company network so that all members of the network can access and update the database.

When a person needs to access the database outside of the network, he/she makes a copy of the database. The database can later be manually imported into the network database.

Right now I'm working on a module that will import the edited database. I do have a way of doing this by opening each table in a recordset and editing each field.

I was wondering if there was a way to "cut and paste" an entire record into a database using code. Example:
''''''''''''''''''''''''''''''''''''''''''
'Instead of saying the following for each table

'rst is a table in the network database
'rstimport is a table in the tmp database that will be added/updated to rst

With rst
.FindFirst "project_num = '"& prj &"'"
If .NoMatch = False Then
.Edit
!proj_name = rstimport!proj_name
!quan = rstimport!quan
!uc = rstimport!uc
.Update
End If
End With

'''''''''''''''''''''''''''''''''''
'I would like to say this and loop it for all the tables

rst.FindFirst "project_num = '"& prj &"'"
rstimport.FindFirst "project_num = '"& prj &"'"
rst = rstimport


Thanks for any help.

 

[dcx693]

You can't just run an update query?

 

[fernin8r]

You may hate me for saying this but I've never used Update Query. I'm going to do a little research on the Access help.

If you could get me started on any steps or procedures to using Update Query, I would very much appreciate it.

Thanks

 

[dcx693]

No problem. I think an update query will do exactly what you need. Check out the links in this thread: Updating a table .

 

[fernin8r]

Update query looks nice.

Just a couple of questions though.

1.) the two tables i have are in two different databases (copies of each other, but different). Will this be a problem or do i need to import the data into the network database before i can run the Update Query?

2.) Can i run Update date query in a module?

Again, Thanks for your help

 

[SforSoftware]

1. Link the tables into the db where you want to create de query. When you've writing rights on the networkdb's, there will be no problem.

2. Yes, Create the query with the querydesigner, then save it and from VBA run DoCmd.OpenQuery "myquery"
Or:
Switch to SQL-view of the query, copy it and give in VBA the command: DoCmd.RunSQL "UPDATE..." (your SQL-string between de quotes)