ADO OpenDatabaseConnection

[Guest]

What am I doing wrong Any help would be great
Getting Error -2147467259(800004005)
[Microsoft][ODBC Driver Manager] Data source not found name not found no
default driver specified

Here is my code

Option Compare Database
Sub OpenDataBaseConnection()

Dim objConn As ADODB.Connection
Dim objRST As ADODB.Recordset
Dim strSQL As String
Dim strConn As String

'Create the ADO Connection and RecordSet Objects

Set objConn = CreateObject("ADODB.Connection")
Set objRST = CreateObject("ADODB.RecordSet")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Documents and Settings\Mike Jones\Desktop\" _
& "Payroll_1.2.mdb;Persist Security Info=False"
strSQL = "Select * from tblRegions;"

objConn.Open
objConn.Mode = adModeRead

objRST.Open strSQL, objConn, adOpenForwardOnly, adLockBatchOptimistic
objRST.MoveFirst

While Not objRST.EOF
Debug.Print objRST.Fields("RegionID")
Debug.Print objRST.Fields("RegionName")
Debug.Print objRST.Fields("EmployeeID")
objRST.MoveNext

Wend
objRST.Close
objConn.Close

Set objRST = Nothing
Set objConn = Nothing

End Sub

 

[Scott McDaniel]

What am I doing wrong Any help would be great

You're not passing objConn the connection info:

Getting Error -2147467259(800004005)
[Microsoft][ODBC Driver Manager] Data source not found name not found no
default driver specified

Here is my code

Option Compare Database
Sub OpenDataBaseConnection()

Dim objConn As ADODB.Connection
Dim objRST As ADODB.Recordset
Dim strSQL As String
Dim strConn As String

'Create the ADO Connection and RecordSet Objects

Set objConn = CreateObject("ADODB.Connection")
Set objRST = CreateObject("ADODB.RecordSet")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Documents and Settings\Mike Jones\Desktop\" _
& "Payroll_1.2.mdb;Persist Security Info=False"
strSQL = "Select * from tblRegions;"

----- CHANGE HERE -----

objConn.Open strConn

objConn.Mode = adModeRead

objRST.Open strSQL, objConn, adOpenForwardOnly, adLockBatchOptimistic

'/MoveFirst isn't needed with ADO
'/objRST.MoveFirst

While Not objRST.EOF
Debug.Print objRST.Fields("RegionID")
Debug.Print objRST.Fields("RegionName")
Debug.Print objRST.Fields("EmployeeID")
objRST.MoveNext

Wend

objRST.Close
objConn.Close

Set objRST = Nothing
Set objConn = Nothing

End Sub

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[Guest]

Thank You Scott
One more thing how could I have this recordset goto a Excel worksheet

What am I doing wrong Any help would be great

You're not passing objConn the connection info:

Getting Error -2147467259(800004005)
[Microsoft][ODBC Driver Manager] Data source not found name not found no
default driver specified

Here is my code

Option Compare Database
Sub OpenDataBaseConnection()

Dim objConn As ADODB.Connection
Dim objRST As ADODB.Recordset
Dim strSQL As String
Dim strConn As String

'Create the ADO Connection and RecordSet Objects

Set objConn = CreateObject("ADODB.Connection")
Set objRST = CreateObject("ADODB.RecordSet")

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" _
& "Data Source=C:\Documents and Settings\Mike Jones\Desktop\" _
& "Payroll_1.2.mdb;Persist Security Info=False"
strSQL = "Select * from tblRegions;"

----- CHANGE HERE -----

objConn.Open strConn

objConn.Mode = adModeRead

objRST.Open strSQL, objConn, adOpenForwardOnly, adLockBatchOptimistic

'/MoveFirst isn't needed with ADO
'/objRST.MoveFirst

While Not objRST.EOF
Debug.Print objRST.Fields("RegionID")
Debug.Print objRST.Fields("RegionName")
Debug.Print objRST.Fields("EmployeeID")
objRST.MoveNext

Wend

objRST.Close
objConn.Close

Set objRST = Nothing
Set objConn = Nothing

End Sub

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com