Importing a Table from one database to another

[TwoOdd]

I'm hoping someone can help me find a more effecient way of importing a table from one access db to another. Right now I'm setting table1 from db1 to a recordset variable. Then I loop through the recordset and insert one record at a time into table2 in db2. The two tables are identical in structure. Is there a way I can run a line of code like:
"INSERT INTO Table2 SELECT Table1.* FROM Table1;"

Granted, the tables are in different mdb's and I know the above code works if both tables are in the same mdb, but I don't have that luxury.

The way my code is written now works, but I'm importing over 80,000 records and it takes about 20 minutes. When I run the line of code mentioned above, it takes about 10 seconds. I would like to be able to speed up the 20 minute code and hope that someone out there can help.

Thanks,


TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[vedicman]

I am successfully using this code to import an access table from one access db to another. The code is written in the db that the tables are being IMPORTED TO.

Sub ImportTable()

DoCmd.TransferDatabase acImport, "Microsoft Access", "Path\NameOfDatabaseToImportFROM.mdb", acTable, "NameOfTableToImport", "NewTableName"

End Sub

I hope this is helpful......Franco

 

[Veep]

Why don't you link table2 to db1? Then you should be able to use "INSERT INTO Table2 SELECT Table1.* FROM Table1;"

 

[TwoOdd]

Franco,

That works from a db standpoint, but does it work in ASP? Can I execute that line of code from ASP?


Veep,

Sorry I wasn't more specific. About once a month, I get an update of table2 but I have to keep all of the previous tables so they become named something like this: table0103, table0203, table0303 -- Notice Month and then Year. In order to link the tables, I would have to download the db and create the link every month. I don't want all of these in my main database so I can keep the file size to a minimum. Every month, I delete the previous months info from the table in my main database and then I have to import the current month's info from the temp db into the main db. I'm trying to do this via ASP because the db is about 60 megs and it is very time consuming to download the entire db, update it and then upload it back to the webserver.

I hope this helps explain a little better what I'm trying to do. If you need more info please let me know.

Thank you both for your help,


TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[vedicman]

I was assuming that you were doing the import at the db level. I don't think you can run that line of code in ASP, but I'm not certain about that.

Franco

 

[TwoOdd]

Thanks for trying. I'm sorry I forgot to mention in the original post that this was via ASP. It's much easier at the db level, but when the db is on an external webserver, it takes about 15-20 minutes to download and another 45-50 minutes to upload. I'm hoping to get around having to download the db.

I also found out the the way the code is currently written is making the db explode to over 200 megs. That's due to the text fields all set to 255, so access is using up all 255 characters for every text field for over 80,000 records.

Because of that, I have to either download it or find another way of doing the import remotely. I'm thinking I might be able to run a macro that does the transferdatabase code, but I haven't gotten the code written to test that yet. If you think of anything else, I would appreciate it.

Thanks,

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[Veep]

Have you tried:
SELECT db1.Table1 * INTO db2.NewTableName
FROM db1.Table1

I'm sure you probably thought of it already but it's really worth a few bucks a month to have a SQL Server if your provider offers it.

 

[TwoOdd]

SQL Server is out of the question for now. There are a lot of easier ways of solving this problem, but my hands are tied and it's got to be with access.

I see where you're trying to go with the code, I thought of trying that but I'm confused. In order to connect to the db I have do something like db.open

So, if I have 2 db's, I have to open both:

db1.open
db2.open

How do I reference both connections in one SQL statement? I mean in order to execute the SQL I would write:

db1.execute(sql)

or

db2.execute(sql)

Beings there is no link between the two, how would either know where to look to get the table in the other db? If you could shed some more light on this I would appreciate it.

Honestly, I'm not sure this is possible, but I've done a lot of things I originally thought were impossible. So, I hoping there is a way to accomplish this with the tools I have to work with.

Thanks for your help,


TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[TwoOdd]

Another way to look at it: Is there a way to insert the whole recordset at once or do I have to insert the recordset one record at a time?

Just brain-storming right now.

TwoOdd
--------------
Good judgment comes from experience, and experience comes from bad judgment.
-- Barry LePatner

 

[ca8msm]

If it is just an access database could you not just create a linked table in your second database which looks at the the table in the first database?

----------------------------------------------------------------------

Need help finding an answer?

Try the search facilty (

http://www.tek-tips.com/search.cfm)

or read FAQ222-2244 on how to get better results.