Select and import table between Access files

[Rakesh935]

Hi Guys,

I have been asked to work on a project in which I need to select and import a specific table from a access file located in shared drive to an access file located in my local drive.

So far I have always dealt with importing excels files into access and never dealt in importing access table into access files.

I did some google research and found out some codes and post executing the codes I see all the tables in access file located in the shared file getting imported into my local drive access file.

If I have to summarize, I would be needing a browser "import object" in order to select and import a specific table.

Request all, please guide me with your advice.

note: the above process needs to be done through a button control over a form.

Thanks and regards,
Rakesh

 

[CJ_London]

why not just link to the table? No import required and always up to date

 

[isladogs]

Just go to External Tools ... Import ... Access ...
Select your database and you will get a list of database objects - tables / queries etc
You can choose to import or link the tables which is generally better (as CJL has stated)

Alternatively if you open both databases and place them side by side on your desktop, you MAY be able to drag the tables from one to the other (it isn't always possible)

 

[Rakesh935]

Linking would not be a good idea since the access file located in shared folder stores all the tables for 1 day and next day all of them are deleted, since the access file happens to be a data extraction point from another tool (.net application)

Hope I am clear, if not then please let me know.

 

[Rakesh935]

Just go to External Tools ... Import ... Access ...
Select your database and you will get a list of database objects - tables / queries etc
You can choose to import or link the tables which is generally better (as CJL has stated)

Alternatively if you open both databases and place them side by side on your desktop, you MAY be able to drag the tables from one to the other (it isn't always possible)

Thanks Ridders for your reply....

But I was thinking, can't we do this pragmatically?

I meant the way we do it excel files like browse->select the excel file->Import

 

[CJ_London]

having linked, you would just have a query to append the data to a local table or whatever you are doing with it

import a specific table from a access file

you know the file name and location, you know the file name. So why do you need a browser? just use docmd.transferdatabase

 

[sxschech]

If this is true:

you know the file name and location, you know the file name.

Then alternatively, this can be done with a query (No linking or importing involved) by using the IN statement.

Example:

SELECT *
FROM externalTable IN 'C:\TEMP\EXTFILE.ACCDB'

Could be more complicated query such as adding where clause or it could be an append query or a make table query into a local table.

 

[jdraw]

Rakesh935,

Are you doing this in Excel now and are trying to use Access?

I meant the way we do it excel files like browse->select the excel file->Import

Can you tell us a little about the process since you delete the records daily?

 

[Cronk]

To display a list of tables in an external Access database, set a database object to the that database and loop through the table names. Exclude system tables.

dim ws as workspace, db as database, tdf as tabledef
set ws = dbengine(0)
set db = ws.opendatabase("yourAccessDB.accdb")
for each tdf in db.tabledefs
   debug.print tdf.name
next