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;"
Surely better to just link to the first table from the second DB?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.
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.Why do you want to duplicate data?
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.I then want the database table data to automatically exported to another database table with the same table name and field names.
In my understanding, this is not duplication, but rather a merging of data that was recorded in separate databases.The data contains answers from a survey and I want to combine all users' data into one table in another database.
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
Yes@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.
I tried the "Link" statement for the tables needed for the other database table.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'm using MS AccessIn 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).
Code: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
Yes@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.
Doesn't seem to match your original requirement and would not work where you answered 'yes' to my question.I'm looking into using one (1) database and hiding 'Buttons' and files
Me too.I'm using MS Access