Error when connecting to ADO database 1

[Matrix03]

when I try and connect to this database I get the same error everytime. Invalid use of a New Key Word for the "New Connection" How can I correct this?


Private Sub Form_Load()


Dim Treespraying As Connection
Set Treespraying = New Connection

Treespraying.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\TreeSpraying.mdb"

Set adoRecordset = New Recordset
adoRecordset.OpenRecordset "Select CustomerLastName, CustomerFirstName", Treespraying, adOpenStatic, adLockOptimistic

Set txtCustomerLastName.DataSource = adoRecordset
txtCustomerLastName.DataField = "CustomerLastName"
Set txtCustomerFirstName.DataSource = adoRecordset
txtCustomerFirstName.DataField = "CustomerFirstName"

End Sub

 

[MatsHulten]

Dim Treespraying As ADODB.Connection and don't forget the reference to ADO 2.6

-Mats

 

[Terpsfan]

This modified code should create the connection you need:

Dim Treespraying As ADODB.Connection
Dim adoRecordset As ADODB.Recordset
Set Treespraying = New ADODB.Connection

Treespraying.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\My Documents\TreeSpraying.mdb"

Set adoRecordset = New ADODB.Recordset
adoRecordset.OpenRecordset "Select CustomerLastName, CustomerFirstName", Treespraying, adOpenStatic, adLockOptimistic

Also remember that as you create a new connection you want to explicitly close the connection when you are done, otherwise the connection still runs in memory. Try this code to close your connection.

Form_Unload()
adoRecordset.Update
adoRecordset.Close
Set adoRecordset = Nothing
TreeSpraying.Close
Set TreeSpraying = Nothing

 

[Matrix03]

I input the code that omega36 gave me but I am still getting one more error. At the adoRecordset.OpenRecordset vb is telling me that method or data member not found. How can I fix this.

 

[MatsHulten]

Small typo from Omega36 I belive. The method is not. OpenRecordset but .Open

Good Luck
-Mats Hulten

 

[Matrix03]

Here is the new error. No value given for one or more parameters. vb highlights the entire line beginning with adoRecordset.open

 

[SauerC]

Your SQL statement is wrong..

&quot;Select CustomerLastName, CustomerFirstName from <insert table name here>&quot;

for example .. if the name of the table is Cust then it would be

&quot;&quot;Select CustomerLastName, CustomerFirstName from Cust&quot;

 

[Matrix03]

Thanks for all the help I have finally connected