Problem with the following code

[Guest]

Can anyone tell me what may be wrong with the following code?
It generates an error when it tries to execute the .Open for the recordset.
I am trying to connect to the database, open the table, and bring data back
to an excel spreadsheet. Also, is the same procedure used to open a query
that is stored in the access database that is used to open a table.

Any help would be greatly appreciated.
Thanks
Vince


Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, c As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.Oledb.4.0; " & _
"Data Source=C:\Access Data\DoctorBlades.mdb;"

Set rs = New ADODB.Recordset

With rs
.Open "tblDoctorBlades", cn, adOpenDynamic, adLockOptimistic
End With

 

[DMoney]

Give this a shot.

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As
Long, c As Long

cn.ConnectionString = "Provider=Microsoft.Jet.Oledb.4.0; "
_
& "Data Source=C:\Access Data\DoctorBlades.mdb;"
cn.Open
cn.CursorLocation = adUseClient
rs.Open "tblDoctorBlades", cn, adOpenDynamic,
adLockOptimistic

 

[DMoney]

I believe this is more like what u are trying to do. U
will need to sub the dbname, tablename, and fieldname for
this code to work.


Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As
Long, c As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.Oledb.4.0; " & _
"Data Source=d:\Db10.mdb;"

Set rs = New ADODB.Recordset
Dim strsql As String
strsql = "SELECT [Company] FROM Customers "
rs.Open strsql, cn

Do Until EOF = True
rs.MoveFirst
ActiveCell = rs![Company]
ActiveCell.Offset(rowoffset:=1).Activate
rs.MoveNext
Loop
rs.Close
cn.Close

 

[Jamie Collins]

Can anyone tell me what may be wrong with the following code?
It generates an error when it tries to execute the .Open for the recordset.

Dim cn As ADODB.Connection, rs As ADODB.Recordset, r As Long, c As Long
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.Oledb.4.0; " & _
"Data Source=C:\Access Data\DoctorBlades.mdb;"

Set rs = New ADODB.Recordset

With rs
.Open "tblDoctorBlades", cn, adOpenDynamic, adLockOptimistic
End With

Your code works for me. What error are you getting?

Jamie.

--