Type Mismatch on ADODB.Recordset

[GreggaR0und]

Hello,

I am debugging the following lines of code and am getting a "type mismatch 13" error on the Set rs = line.

Dim rs As Recordset

Set rs = CreateObject("ADODB.Recordset")

I am running MS-ACCESS 2000 and have added ADO library 2.8. I also have defined my ODBC connection to the network.

What am I doing wrong?

Thanks,
Gregg

[MMcCarthy]

You have to define the database.

Dim db As Database
Dim rs As Recordset
 
Set db = CurrentDB
Set rs = db.CreateObject("ADODB.Recordset")
 

[pks00]

Hello,

I am debugging the following lines of code and am getting a "type mismatch 13" error on the Set rs = line.

Dim rs As Recordset

Set rs = CreateObject("ADODB.Recordset")

I am running MS-ACCESS 2000 and have added ADO library 2.8. I also have defined my ODBC connection to the network.

What am I doing wrong?

Thanks,
Gregg

Why are you creating an object if u have the reference checked? There is no reason to do this, just stick with new. Also u have have DAO checked and that also has a Recordset property so its always best to specify which one

eg

dim rs as ADODB.Recordset

set rs = new ADODB.Recordset


for createobject, u would typically use it this way

dim rs as object

set rs=createobject("ADODB.Recordset")



Hi Mary, long time no see :)
question for you: dont u use currentdb for DAO?

to use the current database for ado, u would use
currentproject.connection

eg

rs.Open "mysql", currentproject.connection, adOpenStatic, adLockPessimistic

[ADezii]

Hello,

I am debugging the following lines of code and am getting a "type mismatch 13" error on the Set rs = line.

Dim rs As Recordset

Set rs = CreateObject("ADODB.Recordset")

I am running MS-ACCESS 2000 and have added ADO library 2.8. I also have defined my ODBC connection to the network.

What am I doing wrong?

Thanks,
Gregg

The correct Syntax is:

Dim MyRS As ADODB.Recordset
Set MyRS = New ADODB.Recordset

[pks00]

there you are, the moderator also agree's with me so we cant be wrong :)

[MMcCarthy]

Hi Mary, long time no see :)
question for you: dont u use currentdb for DAO?

to use the current database for ado, u would use
currentproject.connection

eg

rs.Open "mysql", currentproject.connection, adOpenStatic, adLockPessimistic

Hi nice to see you back. Sorry guys I was tired doing this and since I invariably do DAO rather than ADO myself I wasn't paying attention.

Mary

[ADezii]

Hi nice to see you back. Sorry guys I was tired doing this and since I invariably do DAO rather than ADO myself I wasn't paying attention.

Mary

No explanation needed, Mary.

[GreggaR0und]

Thank you, all. Your information helped me to get a little further along. However, now I am getting a run-time error -2147467259 (80004005) when I go to open the database. The text of the error message is: "Database has been placed in a state that prevents opening or locking."

I'm using the With statement, as shown below, so that I can more easily know what properties are being defined.

My ODBC is defined as a SYSTEM DSN and the driver is a Microsoft Access Driver (*.mdb).

With rs
.Source = "tblMy"
.ActiveConnection = "myODBC"
.LockType = adLockPessimistic
.CursorType = adOpenForwardOnly
.Open
End With

Any help is appreciated.

Thanks,
Gregg

[pks00]

You get that error if the database is already open

Close all instances of the db then try running your code

[GreggaR0und]

Hmmm...that poses a bit of a dilemma. My code is in a VBA Project that is saved in a Module within the .mdb file I'm trying to run. I have to open the database (.mdb file) to access the VB script that I'm trying to execute. I just tried to open another MS Office application (MS Excel) to see if launching Project Explorer would allow it to recognize my Access VBA Project, but it doesn't.

How can I open the source code module without opening the database?

Thanks.

[pks00]

u open the db to run some code. this code, is it reopening same db?