How To Export Data From To Another Database/Table By VBA (1 Viewer)

[lhooker]

I need to export data from a table in a database to another database table (same table name) using VBA ? Examples are welcomed. Thanks ! ! !

 

[June7]

You want to export data from active database (where the code resides) to another database? Could just set link to the other db table. But if you don't want to link, sure, I have example.

Dim strExtract As String

strExtract = gstrBasePath & "Editing\ConstructionExtract.accdb"

'delete records from ConstructionExtract tables
CurrentDb.Execute "DELETE FROM Bituminous IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM BituminousMD IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM Concrete IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM Emulsion IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM PGAsphalt IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM SoilsAgg IN '" & strExtract & "'"
CurrentDb.Execute "DELETE FROM SampleInfo IN '" & strExtract & "'"
'insert records into ConstructionExtract tables
CurrentDb.Execute "INSERT INTO Bituminous IN '" & strExtract & "' SELECT * FROM ConstructionBIT;"
CurrentDb.Execute "INSERT INTO BituminousMD IN '" & strExtract & "' SELECT * FROM ConstructionBMD;"
CurrentDb.Execute "INSERT INTO Concrete IN '" & strExtract & "' SELECT * FROM ConstructionCONC;"
CurrentDb.Execute "INSERT INTO Emulsion IN '" & strExtract & "' SELECT * FROM ConstructionEMUL;"
CurrentDb.Execute "INSERT INTO PGAsphalt IN '" & strExtract & "' SELECT * FROM ConstructionPG;"
CurrentDb.Execute "INSERT INTO SoilsAgg IN '" & strExtract & "' SELECT * FROM ConstructionSA;"
CurrentDb.Execute "INSERT INTO SampleInfo IN '" & strExtract & "' SELECT * FROM ConstructionSampleInfo;"

 

[lhooker]

Thank you for responding ! ! ! Let's me further explain, I'm trying to create a process that will allow a user to enter data into a database table (via MS Access form) and upon exiting (via button) automatically export the data into another database table. That is . . . automatic export of data into another database table.

 

[The_Doc_Man]

If I correctly read what you just described, the answer is a qualified "NO" - unless you take that update & exit after EACH RECORD.

If you want to have an automatic export from a form, the optimum time/place to do that is in the form's _AfterUpdate routine, where you still have everything on that form. In that moment you could build an INSERT INTO operation using the data still sitting in the controls of the form. By contrast, if you do something to each of several distinct records (even something as simple as data entry) and THEN click that "update & exit" hypothetical button, you force yourself to remember what you did during the session so that you can go back and re-apply it. BUT if you catch your data after each individual <form>_AfterUpdate event, everything you need is still there, waiting to be harvested.

 

[June7]

Why do you want to duplicate data?

 

[Gasman]

Thank you for responding ! ! ! Let's me further explain, I'm trying to create a process that will allow a user to enter data into a database table (via MS Access form) and upon exiting (via button) automatically export the data into another database table. That is . . . automatic export of data into another database table.

Surely better to just link to the first table from the second DB?

 

[lhooker]

Why do you want to duplicate data?

I'm trying to retrieve data from a database table (create by a form within the database) to another database table. In other words, the users enters data, then after completion (creation of a report via table). I then want the database table data to automatically exported to another database table with the same table name and field names. The data contains answers from a survey and I want to combine all users' data into one table in another database. I to create an 'Insert' query, but I received a error. Below is the code.

INSERT INTO [User Questions_Answers] * IN "C::\Survey\[Assessment.mdb]"
FROM Survey IN "C:\Survey\Survey.mdb";

 

[June7]

Doesn't follow my example. Which database is this code in? Are you building SQL in query object or VBA?

Why don't you just set link to destination table?

 

[MarkK]

I then want the database table data to automatically exported to another database table with the same table name and field names.

FWIW, the reason you create a database in the first place is to avoid the friction of moving data around. The roots of the word inform you it is a BASE for your DATA, and as such it makes itself available as a repository of raw goodness, to diverse consumers, for diverse purposes, and all from ONE convenient central authoritative source and location.

For one purpose, make one database. Put data produced for that purpose in that database. Diversify your consumers, sure, but centralize your source, and don't make your source a moving target.

 

[CJ_London]

@lhooker What is the background to this? Sounds like you are trying to use synchronisation/replication - users are out in the field and populating local table(s) and when they return to the office the contents of these tables are uploaded to a master table.

 

[GPGeorge]

Please explain, as a narrative, the business reason for duplicating the data in this particular way. It does, indeed, sound like a replication problem, as @CJ_London states. If so, there are other approaches that probably make more sense in most situations.

 

[ebs17]

The data contains answers from a survey and I want to combine all users' data into one table in another database.

In my understanding, this is not duplication, but rather a merging of data that was recorded in separate databases.

@lhooker
You must first understand where the append request code is being executed.

There's the source database, there's the target database, and if the code doesn't run in one of those two databases, there could be a third database just for that. This then depends on which database receives the DAO reference and is therefore considered the “current” database. Such a reference can only be created to one database, so the second database is then taken into account so that its connection data is included directly in the SQL statement.

Your approach in #7 looks like the one with three databases (source / target / control + code execution).

Dim dbT AS DAO.Database
Dim sSQL As String

Set dbT = Opendatabase("C:\Survey\Assessment.mdb")
sSQL = "INSERT INTO [User Questions_Answers] ([FieldList])" & _
     " SELECT [FieldList] FROM [C:\Survey\Survey.mdb].Survey"
dbT.Execute sSQL, dbFailOnError

dbT.Close

 

[lhooker]

@lhooker What is the background to this? Sounds like you are trying to use synchronisation/replication - users are out in the field and populating local table(s) and when they return to the office the contents of these tables are uploaded to a master table.

Yes

 

[lhooker]

Doesn't follow my example. Which database is this code in? Are you building SQL in query object or VBA?

Why don't you just set link to destination table?

I tried the "Link" statement for the tables needed for the other database table.
The data did appear, but when I deleted the records from the source table, it was also
deleted from the other table (receiving table). When the user enters their information
I want to delete it after their review and printing of their information. I do not want the user's information
around for the next user. Also, I want keep all information captured for the receiving
database table for a summary.

 

[lhooker]

In my understanding, this is not duplication, but rather a merging of data that was recorded in separate databases.

@lhooker
You must first understand where the append request code is being executed.

There's the source database, there's the target database, and if the code doesn't run in one of those two databases, there could be a third database just for that. This then depends on which database receives the DAO reference and is therefore considered the “current” database. Such a reference can only be created to one database, so the second database is then taken into account so that its connection data is included directly in the SQL statement.

Your approach in #7 looks like the one with three databases (source / target / control + code execution).

Dim dbT AS DAO.Database
Dim sSQL As String

Set dbT = Opendatabase("C:\Survey\Assessment.mdb")
sSQL = "INSERT INTO [User Questions_Answers] ([FieldList])" & _
     " SELECT [FieldList] FROM [C:\Survey\Survey.mdb].Survey"
dbT.Execute sSQL, dbFailOnError

dbT.Close

I'm using MS Access

 

[lhooker]

@lhooker What is the background to this? Sounds like you are trying to use synchronisation/replication - users are out in the field and populating local table(s) and when they return to the office the contents of these tables are uploaded to a master table.

Yes

 

[June7]

"when I deleted the records from the source table, it was also deleted from the other table (receiving table)"

This makes no sense. That would mean these are the same table.

Clarify situation. Do you have 2 databases or 3? What are their names? Which one has the code?

 

[lhooker]

Two (2) databases . . . I'm looking into using one (1) database and hiding 'Buttons' and files. This may solve my problem.

 

[CJ_London]

I'm looking into using one (1) database and hiding 'Buttons' and files

Doesn't seem to match your original requirement and would not work where you answered 'yes' to my question.

 

[ebs17]

I'm using MS Access

Me too.