Sending Records From One Database To Another

[Access4444]

I am trying to build a database that will allow for records to be transferred from one database to another. Basically each database would house separate data sets that have identical tables with different information. I would like the user to be able to choose from a drop down which database to transfer the record to. An example would be, moving one record from the Texas database to the Florida database because the state has changed. I am currently trying to re-work the below query to point at a text or combo box to adjust the value.


Query1:
INSERT INTO Table1 IN 'C:\Users\Name\Documents\Database 2.accdb'


SELECT *
FROM Table1;

I would like to be able to have "C:\Users\Name\Documents\Database 2.accdb" come from the Form1 control textbox named DirectoryPath.

Thanks!

[JoeM]

Why not just have one central back-end database that houses ALL the data in one place?
You could include a State field in your table to indicate which state it is for, and then use queries to limit to see whatever states you want.

[aytee111]

This will change the query before you run it. I would guess there is a button to run the query, in which case it will go into the OnClick event.

    Dim qdf As QueryDef
    Set qdf = CurrentDb.QueryDefs("query_name")
        
    qdf.SQL = "INSERT INTO Table1 IN '" & Me!Database & "' SELECT * FROM Table1;"
    Set qdf = Nothing
    DoCmd.OpenQuery "query_name"

[Bulzie]

How many databases are you dealing with? Do you have the ability to change the process or structures as JoeM suggests? Another method could be to create a new Database and link all the other database tables to this new database and do all the processes there.

Also seems you are saying move the record as opposed to copy? If so you will need to delete the record from the first table after you copy it to the 2nd one.

[Access4444]

Hi All,

thanks for the quick reply!

Yes Bulzie I would be deleting the record from one table and copying it to another. Aytee111, I could have multiple tables in one central database and then simply copy the records within database or a linked back-end could work very well too. I reason for intentionally having multiple databases instead of one database with multiple tables is the nature of the data and that worry that somehow the data could get crossed (unlikely) and audiences would see other datasets (not ok). with that said I am open to thoughts. To delete the record, I was planning on pushing said record to a query, appending that record to the second table and then with a delete query deleting it from the first table. Sort of long division, so if there's a better way, I am open.

[aytee111]

Your method looks sound - if security is your main concern then having separate databases is the ultimate way to go, otherwise you would have to build a lot of that security into the one database so that users don't see other data. Append and delete, there is no other way. Make sure your criteria matches!

[Access4444]

Ok. That's great to hear that my approach is sound, however my code to send records between separate database tables is not working. I have attached a sample of the database. I can handle creating the query to use to append and delete, but the SQL or code that sends the results of "Query1" to "Table1" in "Database 2" I cannot get. I would be placing the exact directory path in the textbox on "Form1".

I've been trying to modify the below code to just grab the directory path from the textbox named "DirectoryPath" on "Form1"

Private Sub SendRecord_Click()
Dim strSQL As String
strSQL = "INSERT INTO Table1 IN 'C:\Users\Documents\Database 2.accdb' SELECT * FROM Table1"
DoCmd.RunSQL strSQL
End Sub


Instead of 'C:\Users\Documents\Database 2.accdb' something like Form!Form1!DirectoryPath is not working and in said text box is that exact string "C:\Users\Documents\Database 2.accdb" (without quotes)Database 3.zip

[JoeM]

Your method looks sound - if security is your main concern then having separate databases is the ultimate way to go, otherwise you would have to build a lot of that security into the one database so that users don't see other data. Append and delete, there is no other way. Make sure your criteria matches!

One thing you need to be very careful of is confirming that the record was successfully added to the other database before deleting it out of the current one. That is the danger with this sort of method (appending/deleting). The potential for record loss exists, due to a variety of factors (database locks, connection issues, duplicate record violations, incorrect queries). I would consider having whatever process deletes your data to also export a text file with that record that you can archive, so you have those details in case the record is lost.

One thing that you should do, if you have not already, is make sure that ALL your databases are split into front-ends/back-ends, so each user is working off of their own copy of the front-end (all the data is stored in separate back-ends which the front-ends link to). Otherwise, if you have unsplit databases that multiple users are accessing, it is just a matter of time before you run into corruption issues and potential data loss.

[Access4444]

I think I got it!!!!!! and it worked!

The onclick event carries this code


Private Sub Command3_Click()
Dim strSQL As String
strSQL = Me!DirectoryPath
strSQL = "INSERT INTO Table1 IN '" & strSQL & "' SELECT * FROM Table1"
DoCmd.RunSQL strSQL
End Sub

thoughts

[ssanfu]

DoCmd.RunSQL strSQL

Personally, I use

CurrentdB.Execute strSQL, dbFailOnError

"DoCmd.RunSQL" handles the SQL through Access, "CurrentdB.Execute" is handled directly by the database engine (JET/ACE).


And you might think about giving informative names to objects.
It appears that you have a button named "Command3". Maybe name it "btnMoverecord", "cmdMoveRecord", "btnTransferRecord", "btnMoveToExDB" or ???

Much easier to understand what code does if you rename Access default names...especially when debugging or 6 months down the road.

[Access4444]

Great point to rename the objects and thanks for ".Execute" vs ".RunSQL" tip.

Thanks all!

[Bulzie]

JoeM makes a great point about the possibility of losing records if the append does not happen but the delete does for the various reasons he states. Maybe before you do the delete, lookup that record in the new table (that you just appended) to make sure it is there and if so, go ahead with the delete. Better safe then sorry.