file picker dialog and docmd transfer database table

[REBBROWN]

Hey Guys,
any and all help here will be VERY MUCH appreciated.
two issues, one I have figured out, it’s just trying to get what I have figured out ‘plugged’ into what I have not.
SCENARIO:
once a month a user will need to open an Access Database and import a table (actually several tables from different Databases) into the one they are working off of.
that part I have figured out and here is the code I have which miraculously seems to work.

DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\Import\VFATEST.mdb", acTable, "SCM_Data", "Data1", False

As you can see from this, it is accessing the database VFATEST.mdb in c:/import, the SCM_Data table, and naming it “Data1” once it pulls it in.

Works like a charm but now comes the part that has me banging my head against the wall; ‘file picker’ in VBA.
When they click a button the transfer described above works fine but this is what I need to be able to do.
They click a button and it opens a file picker dialog box where they browse to a folder, select the Access Database they want to pull the table from and select the table.
See where I have “Data1” as the table name once it is imported?
That’s where it really gets fun.
Some of these databases have the same Table name that needs to be imported so they need to be able to name the table once it’s imported like “01_SCM_data, 02_SCM_Data, etc.


I have spent the better part of the day driving myself crazy just trying to get the file picker dialog box and I have dug through (actually copy and paste the code) and at least got it to filter to .mdb or .accdb but no clue how to then go next level down to select table!
Again, any and all help very much appreciated

[June7]

First, why are you creating new tables with the import and not importing to existing table?

Have user input whatever they want for table name into an unbound textbox. Reference that textbox to get the table name. Or you can construct whatever table name you want within the code.

[REBBROWN]

• First, why are you creating new tables with the import and not importing to existing table?

Probably does not matter aside from it needs to ‘flush’ the existing table, no appends, overwrite all data.

• Have user input whatever they want for table name into an unbound textbox. Reference that textbox to get the table name. Or you can construct whatever table name you want within the code.

Far easier said than done, at least in my case.
Knowledge of VBA is minimal at best which is why I have been struggling just with being able to get the file picker dialog to work let alone not only picking an Access DB but going into thet DB and pulling out only one table at a time

[June7]

Repeatedly creating and deleting objects causes db size to grow and calls for more frequent Compact & Repair. More efficient to just delete all records then insert the replacement records.