Copying across VBE

[Brian C]

I have a template workbook which runs a number of Macros
that get data from Access. I have added a reference to the
ADO library (in VBE Tools, Reference and ticked Microsoft
ActiveX Data Objects x.x Object Library).

However when I copy this workbook (in Windows Explorer)
the copy workbook contains all macros etc. but this
reference is no longer ticked.

Is there some way of copying a workbook so that this is
ticked. Alternatively is there a way I can run a macro or
something to set it automatically.

Thanks for your help.

 

[Tom Ogilvy]

References are stored in the workbook. Unless you mean it is shown as
MISSING because you open it in a location where the path to the reference is
not valid (and that is not what you have said), then it is unusual that
copying from windows explorer would cause the reference to become unchecked.

 

[onedaywhen]

Consider changing your code to use late binding. As a hint, here's
some side by side examples:

Option Explicit

Private Const strPATH As String = "C:\Documents and Settings\jamiec\My
Documents\New_Jet_DB.mdb"

Sub TestLateBound()
Dim oConn As Object
Dim oRs As Object
Dim strSql As String

Set oConn = CreateObject("ADODB.Connection")

With oConn
.CursorLocation = 3 ' adUseClient
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPATH
.Open
End With

strSql = "SELECT RefID, Surname" & _
" FROM PersonalDetails"

Set oRs = oConn.Execute(strSql)

' <code>

oRs.Close
oConn.Close

End Sub

Sub TestEarlyBound()

Dim oConn As ADODB.Connection
Dim oRs As ADODB.Recordset
Dim strSql As String

Set oConn = New ADODB.Connection

With oConn
.CursorLocation = adUseClient
.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & strPATH
.Open
End With

strSql = "SELECT RefID, Surname" & _
" FROM PersonalDetails"

Set oRs = oConn.Execute(strSql)

' <code>

oRs.Close
oConn.Close

End Sub

--