Importing table data from other databases

[The Rev]

My database has gone through several iterations, as have we all, and I'd like to code a button that will browse for an older version of the DB and import some table data from the old into the new. I don't need every field, just some of the ones that users input data in. I know how to code the select file part, but working with the data from the older DB and importing it into my new DB's existing tables is where I am limited in my knowledge of VBA.

I can start simple. Here's my working code:

Const msoFileDialogFilePicker As Long = 3
Dim strSelectedFile As String
Set fdg = Application.FileDialog(msoFileDialogFilePicker)
 
Set objSFolders = CreateObject("WScript.Shell").SpecialFolders
Path = objSFolders("MyDocuments")

 
With fdg
  .AllowMultiSelect = False
  .Filters.Clear
  .Filters.Add "File Type", "*.accdb", 1
  .InitialFileName = Path
    If .Show = -1 Then
      For Each strSelectedItem In .SelectedItems
      'Code to work with data
      
      Next strSelectedItem
    End If
End With

I basically need the data from the fields "Requirement_Validation" and "Requirement_Satisfied" in my Tbl_Requirements copied from the old version to the new version. Both have the identical Requirement_Number field and no new records are in either. How do I code opening the old DB's table and grabbing the data and then pasting it into the new one?

 

[theDBguy]

Hi. Not sure exactly where you need help, but have you tried the TransferDatabase method?

 

[The Rev]

Hi. Not sure exactly where you need help, but have you tried the TransferDatabase method?

I've seen some examples where the new table is deleted and the old DB table is imported. Will this mess with joins?

 

[The Rev]

So, I was wondering if I can just do an Update Query in SQL and just call the SQL in a DoCmd.Run SQL line? Is this the right way to do so?

SQL = UPDATE Tbl_Requirements ( Requirement_Validation, Requirement_Satisfied ) FROM strSelectedItem SELECT _
Tbl_Requirements.Requirement_Validation, Tbl_Requirements.Requirement_Satisfied IN Tbl_Requirements

DoCmd.RunSQL SQL

Would that update my open database's table with the content from the selected database's table?

 

[isladogs]

You could certainly just replace the new table with the old & recreate any relationships if you wish - do a backup first

Otherwise, I suggest you
a) LINK the 'old' table to your current database
b) make a backup ...just in case
c) run each of the following queries in turn (but only as appropriate)
1. Append query to import unmatched records from the old table
2. Update query to restore values from old table where records exist in new table
3. Delete query to remove any new records that are not in the old table ...if you are sure

If you want more methods to consider, see my extended article Synchronise Data - Mendip Data Systems

 

[The Rev]

You could certainly just replace the new table with the old & recreate any relationships if you wish - do a backup first

Otherwise, I suggest you
a) LINK the 'old' table to your current database
b) make a backup ...just in case
c) run each of the following queries in turn (but only as appropriate)
1. Append query to import unmatched records from the old table
2. Update query to restore values from old table where records exist in new table
3. Delete query to remove any new records that are not in the old table ...if you are sure

If you want more methods to consider, see my extended article Synchronise Data - Mendip Data Systems

Not doing this for me. I have assessment leads that need to be able to click a button and select the old database and everything happen in the background. In your article, I don't see how in SQL to "call" the remote table in order to create the join between tables.

Thanks

 

[isladogs]

Fine.
The simple solution is as I said to link the external table to your database so it isn't remote.
Joins can be created between local and linked tables in exactly the same way as between two local tables.

So create the table link(s) then create the queries or equivalent SQL statements. Then create a procedure to run each of them in turn
Finally call that procedure from a button click on a form.
Make sure there are plenty of built-in warnings to ensure end users don't mess up by mistake.

Personally I wouldn't want something like this being done by 'standard users'
Perhaps your assessment leads are sufficiently knowledgeable but its your call.

 

[The Rev]

Fine.
The simple solution is as I said to link the external table to your database so it isn't remote.
Joins can be created between local and linked tables in exactly the same way as between two local tables.

So create the table link(s) then create the queries or equivalent SQL statements. Then create a procedure to run each of them in turn
Finally call that procedure from a button click on a form.
Make sure there are plenty of built-in warnings to ensure end users don't mess up by mistake.

Personally I wouldn't want something like this being done by 'standard users'
Perhaps your assessment leads are sufficiently knowledgeable but its your call.

I trust them to know at least what they are doing here, so that's not an issue. Since the tables have the same name, would this work to link them?

For Each strSelectedItem In .SelectedItems
    DoCmd.TransferDatabase acLink, "Microsoft Access", strSelectedItem, acTable, "Tbl_Requirements", "Tbl_Requirements", False

Next strSelectedItem

 

[theDBguy]

I trust them to know at least what they are doing here, so that's not an issue. Since the tables have the same name, would this work to link them?

For Each strSelectedItem In .SelectedItems
    DoCmd.TransferDatabase acLink, "Microsoft Access", strSelectedItem, acTable, "Tbl_Requirements", "Tbl_Requirements", False

Next strSelectedItem

I think you'll need to use something like:

Me.ListboxName.ItemData(strSelectedItem)

Sent from phone...

 

[isladogs]

No need to use code. Just use the linked tables manager.
Where a local table exists with the same name, a '1' will be added at the end of the linked table's name e.g. tblRequirements1

 

[isladogs]

No problem!

 

[Pat Hartman]

I trust them to know at least what they are doing here, so that's not an issue. Since the tables have the same name, would this work to link them?

I find this worrisome. Allowing a user to do something like this is not a good idea. You are trying to make a a tool for them to use to do something that almost certainly would never be done more than once. Once the old data has ben imported, why would you ever have to go back and do it again.

Rather than trying to automate one-off things, I think it would simply be safer if you were the one who got the old data and integrated it correctly. I wouldn't use any automation at all. I would manually link to the old table, Write a query to get the old data and do something with it and then get rid of the temporary objects. If you think you might have to reuse any of these imports, then the best solution is to create a "conversion database that only you have access to. Let the db link to both the new and the old tables and you can leave the links and you can leave the queries. That lets you reuse them or copy them if that makes sense.

 

[The Rev]

I find this worrisome. Allowing a user to do something like this is not a good idea. You are trying to make a a tool for them to use to do something that almost certainly would never be done more than once. Once the old data has ben imported, why would you ever have to go back and do it again.

Rather than trying to automate one-off things, I think it would simply be safer if you were the one who got the old data and integrated it correctly. I wouldn't use any automation at all. I would manually link to the old table, Write a query to get the old data and do something with it and then get rid of the temporary objects. If you think you might have to reuse any of these imports, then the best solution is to create a "conversion database that only you have access to. Let the db link to both the new and the old tables and you can leave the links and you can leave the queries. That lets you reuse them or copy them if that makes sense.

We do over a hundred assessments per year. Each database is a stand-alone database due to our restrictions on connecting to customer wifi with our company issued laptops. As I update versions and make improvements based on feature requests, I would like to be able to import the data from prior assessments as we need to revisit the site to reassess things that failed. The same team may not be reassessing. It's much simpler to import a few tables worth of data than me busting my hump for every assessment team to have the correct data in their copy of the DB.

 

[Pat Hartman]

Is each customer creating a new db for each assessment? They shouldn't be. If they want to copy data from a previous assessment, you can give them a more tightly focused method of doing that since everything is in the same database.

When you make changes to the BE, do not create a new, empty database. Instead, create a "conversion" db that runs DAO or DDL queries to modify the BE. Of course this "conversion" db should make a backup first and also check versions so they can't run it twice or run it against the wrong BE. This is what I do for the apps that I sell because I don't have access to the customer's computer. A policy I made early on was to not delete columns or tables or even rename them. So the changes made are to add columns or tables or relationships.

 

[The Rev]

Is each customer creating a new db for each assessment? They shouldn't be. If they want to copy data from a previous assessment, you can give them a more tightly focused method of doing that since everything is in the same database.

When you make changes to the BE, do not create a new, empty database. Instead, create a "conversion" db that runs DAO or DDL queries to modify the BE. Of course this "conversion" db should make a backup first and also check versions so they can't run it twice or run it against the wrong BE. This is what I do for the apps that I sell because I don't have access to the customer's computer. A policy I made early on was to not delete columns or tables or even rename them. So the changes made are to add columns or tables or relationships.

I'll explain. We have a database that assessors use to assess the NIST 800-171a objectives. Each assessment is for a different company. Each assessment has 4 assessors who divide the requirements up and a lead assessor who aggregates the assessor data. There are no front end and back ends. It's a single .accdb file with everything contained, and each assessor has a copy. I use query generated Excel spreadsheets to export and import their data. At the end of the assessment, we give a score. In 6 months, we have to revisit them and reassess any requirement that we initially found as not satisfied. It may be a different team. I need the lead to be able to browse for an old copy of the DB from the prior assessment and import the table data into the new database so they don't have to work off an old version that is missing the current features, like new reports that have been added.

Hopefully that clears things up.

 

[Pat Hartman]

Not a good design plan but whatever. The best option is to create a standard import if they want to import all the old data or even just specific data. Do not leave it to them to decide what to input or what version of the database to import from. You can force the import to be from the most recent copy of the database.

The problem with importing all the old data at this point and loading it into the collection tables is it allows the users to be sloppy with the collection and not address every item as they should. If you want to import the old copy for reference, I would put it in separate tables and find a way to make the forms show the old value next to the control for the new value if you want them to see the difference immediately. Otherwise, you can just run reports that show fields with unacceptable deviations.

 

[The Rev]

I don't understand why what I am asking for is so difficult. Maybe if I boil it down... I need to:

1) Select an old database from the msoFileDialogFilePicker (Already have this down)
2) Get all of the [Requirement_Satisfied] field's data from that database's Tbl_Requirements
3) Insert that data into the [Requirement_Satisfied] field of the new database's Tbl_Requirements for the corresponding Requirement.

I can deal with the rest, but for now, this is what I need to do.

 

[Pat Hartman]

It is not difficult. Do exactly what you described. Your original request sounded like you wanted the user to decide to import some fields this time and other fields at a different time. Since you always knw what to import, let the user navigate to the BE. You link to the necessary table and use an append query to bring the data into the active db.

 

[The Rev]

It is not difficult. Do exactly what you described. Your original request sounded like you wanted the user to decide to import some fields this time and other fields at a different time. Since you always knw what to import, let the user navigate to the BE. You link to the necessary table and use an append query to bring the data into the active db.

Again, I don't know exactly how to do the SQL code to make that happen. I tried the following:

"SELECT Tbl_Requirements.Req_Validation INTO Tbl_Requirements IN " & strSelectedItem & " FROM Tbl_Requirements;"

But it gives me the error that it must include a table or a query.

 

[theDBguy]

Again, I don't know exactly how to do the SQL code to make that happen. I tried the following:

"SELECT Tbl_Requirements.Req_Validation INTO Tbl_Requirements IN " & strSelectedItem & " FROM Tbl_Requirements;"

But it gives me the error that it must include a table or a query.

Hi. Did you implement the change I suggested earlier?