How to export records from one db into another db

  • Thread starter Thread starter Dan Thomson
  • Start date Start date
D

Dan Thomson

I have a database which contains 900 records that I'd like to export to
another database.

Both databases have the same tables and fields. The destination database
already contains data in the destination table that I do not want to
overwirte. I want to do something that supports all Access versions starting
with Access 2000.

Does any one have any thoughts on how to do this effectively?


Here is some code that works. I'm just looking to see if there is a better
way to do it.

' Define the name of the table to work with
strTable = "tblBogus"

' Create a reference to the destination table in the user's database
Set rstScripts = dbs.OpenRecordset(strTable, dbOpenDynaset)

' Go to the first record of the source table
DoCmd.RunCommand acCmdRecordsGoToFirst

' Loop through all records in the source table
For s = 1 To intRecordCount
With rstScripts
' Create the new record in the destination database
.AddNew
![Name] = Me![Name]
![Type] = Me![Type]
!
Code: 
 = Me![Code]
![Author] = Me![Author]
.Update
End With

If s = intRecordCount Then Exit For

' Go to the next record to be exported
DoCmd.RunCommand acCmdRecordsGoToNext
Next
 
A better way would be to use an Append query. It's almost always better to
use SQL when you can, rather than using VBA.

BTW, for those time when you do need to loop through a recordset, the
following would be better:

If Not rstScript.BOF And Not rstScript.EOF Then
With rstScripts
Do While Not .EOF
< put your logic here >
.MoveNext
Loop
End With
End If
 
Can you give me an example on using an Append query?

Douglas J. Steele said:
A better way would be to use an Append query. It's almost always better to
use SQL when you can, rather than using VBA.

BTW, for those time when you do need to loop through a recordset, the
following would be better:

If Not rstScript.BOF And Not rstScript.EOF Then
With rstScripts
Do While Not .EOF
< put your logic here >
.MoveNext
Loop
End With
End If

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Dan Thomson said:
I have a database which contains 900 records that I'd like to export to
another database.

Both databases have the same tables and fields. The destination database
already contains data in the destination table that I do not want to
overwirte. I want to do something that supports all Access versions starting
with Access 2000.

Does any one have any thoughts on how to do this effectively?


Here is some code that works. I'm just looking to see if there is a better
way to do it.

' Define the name of the table to work with
strTable = "tblBogus"

' Create a reference to the destination table in the user's database
Set rstScripts = dbs.OpenRecordset(strTable, dbOpenDynaset)

' Go to the first record of the source table
DoCmd.RunCommand acCmdRecordsGoToFirst

' Loop through all records in the source table
For s = 1 To intRecordCount
With rstScripts
' Create the new record in the destination database
.AddNew
![Name] = Me![Name]
![Type] = Me![Type]
!
Code: 
 = Me![Code]
![Author] = Me![Author]
.Update
End With

If s = intRecordCount Then Exit For

' Go to the next record to be exported
DoCmd.RunCommand acCmdRecordsGoToNext
Next
[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
 
Nevermind...

I created the query manually then added some test code to show me what the
SQL was. I now know what the query def is to get this done. Now I can
programmatically create the query at run time.

thanks for the pointer

Dan

Oh Yea, Using this method was significantly faster than using vba!


Dan Thomson said:
Can you give me an example on using an Append query?

Douglas J. Steele said:
A better way would be to use an Append query. It's almost always better to
use SQL when you can, rather than using VBA.

BTW, for those time when you do need to loop through a recordset, the
following would be better:

If Not rstScript.BOF And Not rstScript.EOF Then
With rstScripts
Do While Not .EOF
< put your logic here >
.MoveNext
Loop
End With
End If

--
Doug Steele, Microsoft Access MVP

(No private e-mails, please)



Dan Thomson said:
I have a database which contains 900 records that I'd like to export to
another database.

Both databases have the same tables and fields. The destination database
already contains data in the destination table that I do not want to
overwirte. I want to do something that supports all Access versions starting
with Access 2000.

Does any one have any thoughts on how to do this effectively?


Here is some code that works. I'm just looking to see if there is a better
way to do it.

' Define the name of the table to work with
strTable = "tblBogus"

' Create a reference to the destination table in the user's database
Set rstScripts = dbs.OpenRecordset(strTable, dbOpenDynaset)

' Go to the first record of the source table
DoCmd.RunCommand acCmdRecordsGoToFirst

' Loop through all records in the source table
For s = 1 To intRecordCount
With rstScripts
' Create the new record in the destination database
.AddNew
![Name] = Me![Name]
![Type] = Me![Type]
!
Code: 
 = Me![Code]
![Author] = Me![Author]
.Update
End With

If s = intRecordCount Then Exit For

' Go to the next record to be exported
DoCmd.RunCommand acCmdRecordsGoToNext
Next
[/QUOTE]
[/QUOTE]
[/QUOTE]
Click to expand...
Click to expand...
Click to expand...
 
Back
Top