Import data matching criteria

[chadb]

Is there a way to import data that matches specific critera from one mdb file to the current (open) mdb file?

I am able to use the transferdatabase action (and method) to transfer a specific table from one mdb to the current, but it imports the entire table. I only want to import data that is greater than or equal to today's date from that table.

If there is, then I will need to learn how to delete a table from vba.

Thanks.

 

[llkhoutx]

Import your data into a temporary table, then filter it.

You can't filter while importing.

However, you can link a foreing mdb table, then filter that table's data into the target table.

 

[chadb]

Thanks for the reply. Unfortunately I am trying to avoid importing all of the data. The database is 80mb and I have to import it from our server which is in a different city, so that import takes a long time. I thought by importing select criteria, which would knock it down to about 10mb, I could speed up the process.

I know in Excel you can do a query on an external database and enter critera. When I do it in excel I record a macro and perform the query so I have the vba syntax all done for me. I was hoping there was some way to do this in Access.

 

[llkhoutx]

You've stumbled onto one of the deficiencies of Access, the whole source come across the network.

I had the same problem with a Fortune 100 company.

Can you create an application to run on the remote server to isolate the data you want; then import it?

 

[nateobot]

Can't you link to the 'master' table, then use an INSERT INTO Query and specify your criteria there?

 

[chadb]

I found a workaround for what I'm trying to do. Pretty much what llkhoutx suggested. On the server (terminal server) I'm going to backup the master db file and then run a separate db to link to the backup which will run a deletequery to get rid of the data i dont want. Then, using a bat file, I'll compact and repair the backup file and then just copy the backup db to the specific computer.

Too bad Access can't do a query like in Excel. Thanks for the suggestions and feedback.

 

[Summerwind]

Hi Chad

Either I've misunderstood the question (Not unusual) or I can't see your problem. What I would do is this:

Create a temporary table using ADOX having the fields that you need to incorporate into your current database.

Create a connection to the database on the server.

Use an ADODB command to pull into your temp table the data from the server db.

You now have the data you need with minimum traffic across the network - hence mucho faster than your workaround.

Now use another ADODB command(s) to merge your data with the tables in your current database.