Ado Recordset Run time error '7965'

  • Thread starter Thread starter newdepts via AccessMonster.com
  • Start date Start date
N

newdepts via AccessMonster.com

I am trying to set the variable Ado Recordset to the Recordsource of the
Form, from reading your discussions I thought this would be possible with:

conn.Open GloConnectionString
rst.Open mysql, conn, , adLockOptimistic
Set Me.Recordset = rst

I am getting the following run time error:

Error Message

"The object you entered is not a valid recordset property" on the Set
Me.recordset = rst above.

I would appreciate help on this.

Regards


John
 
conn.Open GloConnectionString
rst.Open mysql, conn, , adLockOptimistic
Set Me.Recordset = rst
Click to expand...

Isn't it easier just to do a

Me.Recordsource = MySQL

What advantage do you see in trying to open the recordset yourself?

Tim F
 
Thanks Tim,

You ask what advantage I see in trying to open the recordset myself

But wouldn't I need to have the Connection string and the SQL all included
in the mysql string?

I haven't done this and don't know the syntax, so I would appreciate your
help.

Below is an attempt at it.

Mysql = "DRIVER={MySQL ODBC 3.51 Driver};" & _
"Server=localhost;" & _
"Port=3306;" & _
"Option=16384;" & _
"Stmt=;" & _
"Database=P_Records;" & _
"Uid=root;" & _
"Pwd=password"

mysql = mysql & " SELECT * FROM mtable"

Now how is it Connection opened if I use:

Me.Recordsource = MySQL?

Regards


John
 
mysql = mysql & " SELECT * FROM mtable"

Now how is it Connection opened if I use:

Me.Recordsource = MySQL?
Click to expand...


Access has two hearts: one is the database engine (aka Jet) and the other
one is a rapid development environment. It's the second one of these that
does all the connection and password stuff for you so that you can
concentrate on the forms and programming.

I don't have a lot of experience with using odbc datasources from a mdb,
but I think the idea is to link the appropriate tables in using Get
External Data -> Link Tables and finding the data sources. Once they are
there, you just base your forms on an appropriate SQL Select statement:

Me.Recordsource = "SELECT * FROM MTable WHERE MNumber = 10445"

and so on.

Hope that helps


Tim F
 
Tim

Thanks for your help. I want to avoid linking in tables that might not be
used in a session in the interest of a smaller footprint, and neater Access-
based application.

I am using ODBC to Mysql as you gather.

I have found this works instead. It creates a pass-through query in the
local database (Frontend) called querytmp, which becomes the recordsource
for the subform in Datasheet View. However, it is not updatabale.

Dim qdf As QueryDef

Set qdf = db.CreateQueryDef("querytmp")
With qdf
.Connect = "ODBC;DSN=mysql;DESC=;DB=p_database;SERVER=localhost;
UID=root;PASSWORD=password;PORT=3306;SOCKET=;
OPTION=0;STMT=;"
.SQL = "SELECT * FROM MTable WHERE MNumber = 10445"
End With
Me.RecordSource = "querytmp"

Worth it in the end.

Regards

and thanks


John
 
Back
Top