Reattach Tables

N

Nigel

I am trying to use code to reattach some tables

this is what I have so far, all I need is some assistance and I can figure
the rest out

The 4 tables are defined

I am using the loop function increase a count by 1 (works) and then add it
to a variable called deltable (this is the wrong kind of loop)

really what should happen is it should scroll thru the variables called
TableA* and delete them and keep going till all the tables are all deleted

Any help would be appreciated

Dim TableA1, TableA2, TableA3, TableA4 As String

TableA1 = "address"
TableA2 = "addsubform"
TableA3 = "buyers"
TableA4 = "catsales"
DoCmd.SetWarnings False
DoCmd.Hourglass True
' Step 1 removes all existing links
DoCmd.Hourglass True
Dim counter
Dim deltable as string

counter = 0
Do While counter <= 4
MsgBox counter
counter = counter + 1
deltable = "tableA" & counter


DoCmd.DeleteObject acTable, deltable

Loop
 
D

Douglas J. Steele

You're storing your table names in variables, and then trying to indirectly
refer to those variables. Access can't do that the way that you're trying.
You're far better off in this situation to define an array and use it.

Dim TableNames(1 to 4) As String

TableNames(1) = "address"
TableNames(2) = "addsubform"
TableNames(3) = "buyers"
TableNames(4) = "catsales"
DoCmd.SetWarnings False
DoCmd.Hourglass True
' Step 1 removes all existing links
DoCmd.Hourglass True
Dim counter
Dim deltable as string

counter = 0
Do While counter <= 4
MsgBox counter
counter = counter + 1
DoCmd.DeleteObject acTable, TableNames(counter)
Loop

BTW, you made a very common mistake in your code.

Dim TableA1, TableA2, TableA3, TableA4 As String

does not define 4 String variables: it defines 1 String variable (TableA4),
and 3 Variant variables (the other 3).

To declare the four all as string variables, you need to use

Dim TableA1 As String, TableA2 As String, TableA3 As String, TableA4 As
String
 
N

Nigel

OK how would I use the array

thanks

Douglas J. Steele said:
You're storing your table names in variables, and then trying to indirectly
refer to those variables. Access can't do that the way that you're trying.
You're far better off in this situation to define an array and use it.

Dim TableNames(1 to 4) As String

TableNames(1) = "address"
TableNames(2) = "addsubform"
TableNames(3) = "buyers"
TableNames(4) = "catsales"
DoCmd.SetWarnings False
DoCmd.Hourglass True
' Step 1 removes all existing links
DoCmd.Hourglass True
Dim counter
Dim deltable as string

counter = 0
Do While counter <= 4
MsgBox counter
counter = counter + 1
DoCmd.DeleteObject acTable, TableNames(counter)
Loop

BTW, you made a very common mistake in your code.

Dim TableA1, TableA2, TableA3, TableA4 As String

does not define 4 String variables: it defines 1 String variable (TableA4),
and 3 Variant variables (the other 3).

To declare the four all as string variables, you need to use

Dim TableA1 As String, TableA2 As String, TableA3 As String, TableA4 As
String
Click to expand...
 
N

Nigel

Sorry I misread the email then saw it, the remove works perfectly

But now I want to reattach so I tried this


Do While counter <= 4
counter = counter + 1
DoCmd.TransferDatabase acLink, "Microsoft Access", path, acTable,
TableNames(counter), TableNames(counter)
Loop

it sees the tablenames(counter) as a number and not a table name, any ideas
 
N

Nigel

Doug,

I rechecked what I had and saw the error, the code works perfectly and I
REALLY appreciate your assistance

Have a nice long weekend

Nigel
 
Top