Connecting Excel to Access Using Late Binding

[Dr. M]

Hello! I'm trying to connect to an Access database using Late Binding but my
little simple code will not open the database. I appreciate your help!!!

Sub Test()

Dim strConnection As String
strConnection = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=M:\database.mdb;"

Dim cn As Object
Set cn = CreateObject("ADODB.Connection")

cn.Open strConnection

' Find out if the attempt to connect worked.
If cn.State = adStateOpen Then
MsgBox "You are connected!"
Else
MsgBox "Sorry. You are not connected."
End If

' cn.Close

End Sub

 

[Office_Novice]

Try This, this worked for me after referencing Active X Data Objects Lib 2.7

Public Const ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C\: YourDataSource Here."

Public Sub Zero()
Dim Connection As ADODB.Connection
Set Connection = New ADODB.Connection
Connection.Open (ConnectionString)

If Connection.State = adStateOpen Then MsgBox "You Have Connected
Successfully ", vbInformation
If Connection.State = adStateClosed Then MsgBox "Sorry, Connection
Failed", vbCritical

If Connection.State = adStateOpen Then Connection.Close
End Sub

 

[Dr. M]

Thanks for the tip. However, I'm trying to avoid having to reference the
Active X Data Objects Lib X.X by clicking it as I'm unsure which machine will
actually be running this code. I was hoping more for a programmtic solution.

Thank you!

 

[Office_Novice]

Sorry About that try this

Public Sub Zero()
Dim Connection As Object
Set Connection = CreateObject("ADODB.Connection")
Connection.Open (ConnectionString)

If Connection = "" Then MsgBox "Fail", vbCritical
If Not Connection = "" Then MsgBox "Passed", vbInformation
End Sub

 

[Bob Phillips]

You can't use the library constants if you use late binding, so replace

adStateOpen

with the value 1

 

[Dr. M]

That seemed to do it! Thanks y'all!!!
--
Dr. M

You can't use the library constants if you use late binding, so replace

adStateOpen

with the value 1

--
__________________________________
HTH

Bob