Converting Linked Tables......

[Mike_In_Ga]

I have an Access Db where I am pulling in (via ODBC/USQL) linked tables to an ERP system (C-ISAM). I would like to be able to convert the linked tables to regular tables. The only way I know how to do this is to run a make table query for each individual linked table. There are several hundred linked tables so this would take a long time.

Is there a way to create "regular" tables from all the linked tables in one step? If so, would it be possible to create the "regular" tables in a seperate Db?

Thx

 

[KenHigg]

Yes you should be able to automate this. Is this a one time deal or will you need to do it on a regular basis?

 

[Mike_In_Ga]

I will need to do it 5 or 6 times. If there are two approaches I'd much appreciate if you could explain both.

Thx

 

[WayneRyan]

Mike,

Just a quick idea:

Dim tdf As DAO.TableDef

For each tdf In CurrentDb.TableDefs
   If Len(tdf.Connect) > 0 Then 
      DoCmd.RunSQL "Select * " & _
                   "Into " & tdf.Name & "_New " & _
                   "From " & tdf.Name
   End If
   Next

Then you can just drop the linked tables and rename the
"_New" ones.

Wayne

 

[KenHigg]

Give me a minute to come up with the code...

 

[KenHigg]

Waynes too fast for me -

Exactly what I was thinking...

 

[Mike_In_Ga]

Do I create a module, copy the code, paste into the module, save the module and then run it? Please forgive my ignorant question

 

[WayneRyan]

Mike,

Just attach it to a Command Button on a form. Especially if you're gonna
do it multiple times.

Wayne

 

[Mike_In_Ga]

I got the following error:

Compile Error:
Invalid Outside Procedure

The following line of code was highlighted:
For Each tdf In CurrentDb.TableDefs

I really appreciate the help

 

[Banana]

Try adding this:

Dim db As DAO.Database

Set db= CurrentDb()

...

For Each tdf In db.TableDefs

Don't know if this is going to fix the error (as I expect Wayne's version to be able to work), though.

 

[Mike_In_Ga]

I am still getting the same error message and the module is hanging on the "Set" command. Below is the entire code for the command button:

Dim db As DAO.Database
Set db = CurrentDb()
For Each tdf In CurrentDb.TableDefs
If Len(tdf.Connect) > 0 Then
DoCmd.RunSQL "Select * " & _
"Into " & tdf.Name & "_New " & _
"From " & tdf.Name
End If
Next

Most tables are linked tables but I do have a few regular tables in the Db. Could that be causing an issue?

 

[KenHigg]

Hum... Waynes worked here.

Would you mind posting your entire block of code?

 

[Banana]

Okay, you definitely shouldn't be getting an error on "Set db=Currentdb()"....

BTW, you need to edit the line-

For Each tdf in [color=red]db[/color].TableDefs 'Don't use CurrentDb

.

Now, I want to make sure- did you put this in the routine like this:

Private Sub [color=red]Name Of your command button[/color]_Click()

....Your code go here

End Sub

?

 

[KenHigg]

Darn... I'm slow today -

 

[Banana]

Actually, Ken, you got in first. That'd count for something, no?

 

[Mike_In_Ga]

Banana,
Here is the full code form the command button--->Event---->On Click--->Event Procedure:

Private Sub Command0_Click()
Dim db As DAO.Database
Set db = CurrentDb()
For Each tdf In db.TableDefs
If Len(tdf.Connect) > 0 Then
DoCmd.RunSQL "Select * " & _
"Into " & tdf.Name & "_New " & _
"From " & tdf.Name
End If
Next
End Sub


I am still getting a compile error on "Set"

 

[Banana]

You also should keep the line "Dim tdf As TableDef", btw.

Look all over the module you're in now and see if there's a extra End Sub or End Function or Private Sub without a End Sub.

 

[KenHigg]

I think he has the entire pc of code outside of a procedure - ?

 

[KenHigg]

G e t t i n g s l o w e r. . .

 

[Mike_In_Ga]

OK,
Not sure what is going on.....here is the code on the command button:

Private Sub Command0_Click()
Dim tdf As DAO.TableDef
Dim db As DAO.Database
Set db = CurrentDb()
For Each tdf In db.TableDefs
If Len(tdf.Connect) > 0 Then
DoCmd.RunSQL "Select * " & _
"Into " & tdf.Name & "_New " & _
"From " & tdf.Name
End If
Next

When I click the command button the VB Mdule pops up and I get

---------------------------
Microsoft Visual Basic
---------------------------
Compile error:
Invalid outside procedure
---------------------------


The VB Module is shown and the word set is highlighted:

Dim tdf As DAO.TableDef
Dim db As DAO.Database
Set db = CurrentDb()
For Each tdf In db.TableDefs
If Len(tdf.Connect) > 0 Then
DoCmd.RunSQL "Select * " & _
"Into " & tdf.Name & "_New " & _
"From " & tdf.Name
End If
Next