VBA Export to another DB string (1 Viewer)

[Jayce72]

This should be an easy one.

I have the following code that works perfectly:

Dim r As Recordset
Set r = CurrentDb.OpenRecordset("tblTableNames")
r.MoveFirst
Do Until r.EOF
If Left(r!tablename, 4) <= "sqlA" Then
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=nod_database_v2;UID=Me;Trusted_Connection=Yes;APP=2007 Microsoft Office system;DATABASE=my_database", acTable, r!tablename, r!tablename
End If
r.MoveNext
Loop


What I want to do is instead of placing the tables into the current DB is to place r!tablename into another DB

which is: "C:\Users\Me\Documents\Test.accdb"

Thanks in advance

 

[RuralGuy]

Do you want to simply copy the Table to the "C:\Users\Me\Documents\Test.accdb"

 

[Jayce72]

Do you want to simply copy the Table to the "C:\Users\Me\Documents\Test.accdb"

Yes I do - but some tables are huge - so I would rather not import into the current and then copy it to the destination DB.

Trying to insert directly into the destination DB

 

[RuralGuy]

I need to give that some thought. So I'm clear, you want to copy an SQL table directly from the SQL back end into an Access DB named "C:\Users\Me\Documents\Test.accdb"

 

[Jayce72]

I need to give that some thought. So I'm clear, you want to copy an SQL table directly from the SQL back end into an Access DB named "C:\Users\Me\Documents\Test.accdb"

Correct - but looping and copying through all the tables in the sql server. I have created a table with all the table names in so it knows which ones to copy

Thanks for your help

Also - I currently use this code that works - but it fails sometimes and locks users out of the database.

CurrentDb.Execute " SELECT [" & tdf.Name & "].* INTO [" & tdf.Name & "] IN 'C:\Users\Me\Documents\Test.accdb' " & _
" FROM [" & tdf.Name & "];", dbFailOnError

 

[RuralGuy]

I'll just throw this out: Have you tried to *export* your ODBC table to the New db?

 

[Jayce72]

I'll just throw this out: Have you tried to *export* your ODBC table to the New db?

Wont this just create another linked ODBC linked table - Im trying to create a backup of the data in sql server. (Also, I dont have sql server management studio to do this server side)

Unless you know another way apart from what Im currently doing:

CurrentDb.Execute " SELECT [" & tdf.Name & "].* INTO [" & tdf.Name & "] IN 'C:\Users\InternetUser\Documents\Test.accdb' " & _
" FROM [" & tdf.Name & "];", dbFailOnError

 

[RuralGuy]

You can *export* a query.

 

[Jayce72]

You can *export* a query.

How would I do this in VBA to loop through the tables without creating over 200 queries

 

[RuralGuy]

Why couldn't you create a static query that take the table name as a parameter?

 

[Jayce72]

Why couldn't you create a static query that take the table name as a parameter?

Not sure how to do that and also I worried about locking users out of the database due to the heavy traffic in using this method. Hence I was trying to access the sql Server directly without slowing down traffic

But I am great full for your assistance

 

[RuralGuy]

Here's another thought. If your "C:\Users\Me\Documents\Test.accdb" already have the *import* function in it and the linked tables you could execute that code from another db. Just trying to think outside of the box.

 

[Jayce72]

Here's another thought. If your "C:\Users\Me\Documents\Test.accdb" already have the *import* function in it and the linked tables you could execute that code from another db. Just trying to think outside of the box.

I see what your thinking - But the way it's set up is that at midnight I have a form on a timer that copies a blank db then copies all the data into it and then renames it to todays date. Therefore, that DB never gets opened and it's all automated

 

[RuralGuy]

All of your ODBC tables are linked tables are they not? I'm certainly not an SQL expert but I believe SQL has its own backup feature doesn't it?

 

[Jayce72]

All of your ODBC tables are linked tables are they not? I'm certainly not an SQL expert but I believe SQL has its own backup feature doesn't it?

Yes they are linked tables - but as mentioned earlier - I dont have access to sql server management studio to run it from there

 

[RuralGuy]

If they are linked tables then the automated backup does not save the data in the linked tables does it?

 

[RuralGuy]

The CopyObject is another process you should probably explore.

 

[Jayce72]

Yes I do - but some tables are huge - so I would rather not import into the current and then copy it to the destination DB.

Trying to insert directly into the destination DB

I tried to import the tables into the current DB and
-then copy the data over to the destination DB
-and then delete the table from the current DB.

Although slow it did work. But, and a big BUT, was that although the tables were deleted the size of the current DB just grew out of control.

I am interested in a changeable export query to export the ODBC linked tables to a destination DB as standalone tables- non linked ODBC

 

[RuralGuy]

I know I sound like a broken record but I've not been able to locate a command that would do it as you describe. If you could create a db that could import the ODBC tables that you want, from within your Access you could copy that db to any name you want and assuming the import launches with the AutoExec or a launched form and exits when complete, you could launch that db from your db and I believe it would do what you want.