Repair broken ADOX link via VBA

S

Stuart

I am developing on a Vista machine and implementing on XP machines. I use
the ADOX library. Vista requires ADOX rev 6 while XP requires ADOX rev 2.8.
When I attempt to run the app on the XP machine (without manually changing
the references) I get a "Missing Reference" error. I need to resolve this
issue via code, so the users don't have to do it. I have written some code
that will write the reference, but I cannot seem to write code that will
delete the missing reference error. This apparently has to be done before I
can write the correct reference. Can anyone suggest a solution?

Thanks, Stuart
 
D

Douglas J. Steele

Why not use Late Binding, so that you don't need to set a reference at all?

That's the best way for ALL references other than the basic ones included by
default.
 
S

Stuart

Doug, thanks. I'd like to use it, but I can't figure out exactly what it is.
I have been looking at various articles since receiving your post, and they
all assume that I know more than i know. Can you suggest any articles that
introduce the topic?

Thanks, Stuart
 
D

Douglas J. Steele

Check what Tony Toews has at http://www.granite.ab.ca/access/latebinding.htm

In a nutshell, you'd need to:

1) Change all your declarations from

Dim tbl As Table

to

Dim tbl As Object

(You should never use Dim tbl As New Table in VBA!)

2) Change all your instantiations from

Set tbl = New Table

to

Set tbl = CreateObject("ADOX.Table")

3) Assuming you use intrinsic constants, you either need to replace the
references with the actual values, or declare the constants in your own
code. For example, if you've got:

tbl.Name = "MyTable"
tbl.Columns.Append "Column1", adInteger
tbl.Columns.Append "Column2", adInteger
tbl.Columns.Append "Column3", adVarWChar, 50
cat.Tables.Append tbl

you either need to change that to

tbl.Name = "MyTable"
tbl.Columns.Append "Column1", 3
tbl.Columns.Append "Column2", 3
tbl.Columns.Append "Column3", 202, 50
cat.Tables.Append tbl

or else add the following to your application:

Const adInteger As Long = 3
Const adVarWChar As Long = 202

As you may be able to guess, step 3 is the real pain: once you remove the
reference and compile your application, you're bound to find constants you
forgot about!
 
S

Stuart

Doug, changing the instantiations as you suggested has completely done the
job. It was really very simple! Thanks so much.

Stuart
 
Top