OpenRecordset with DAO

[Bernie Hunt]

I gave up for now with ADO and am trying to get a DAO connection working. It
is apparently not my week.

Dim ws As Workspace
Dim db As Database
Dim strConnection As String
Set ws = DBEngine.Workspaces(0)
Let strConnection = "ODBC;DSN=hna;UID=;PWD="
Set db = ws.OpenDatabase("", False, False, strConnection)
Dim rs As Recordset
Dim SQLStuff
SQLStuff = "SELECT * FROM Bill"
Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)

I'm getting Run Time Error 3001, Invalid argument.

Can anyone see what's invalid?

Bernie
Getting very frustrated and wondering why he can't figure this out anymore.

[Paul Clement]

On Thu, 8 Feb 2001 01:26:14 -0500, "Bernie Hunt" <bhunt@optonline.net> wrote:

¤ I gave up for now with ADO and am trying to get a DAO connection working. It
¤ is apparently not my week.
¤
¤ Dim ws As Workspace
¤ Dim db As Database
¤ Dim strConnection As String
¤ Set ws = DBEngine.Workspaces(0)
¤ Let strConnection = "ODBC;DSN=hna;UID=;PWD="
¤ Set db = ws.OpenDatabase("", False, False, strConnection)
¤ Dim rs As Recordset
¤ Dim SQLStuff
¤ SQLStuff = "SELECT * FROM Bill"
¤ Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)
¤
¤ I'm getting Run Time Error 3001, Invalid argument.
¤
¤ Can anyone see what's invalid?

Which line of code?

Have you tried specifying the database name argument for OpenDatabase instead of an empty string?


Paul ~~~ pclement@ameritech.net
Microsoft MVP (Visual Basic)

[Arthur Wood]

Bernie,
What line is causing the error. Have you set a breakpoint in your code
and then single-stepped through it?


Arthur Wood


"Bernie Hunt" <bhunt@optonline.net> wrote:
>I gave up for now with ADO and am trying to get a DAO connection working.
It
>is apparently not my week.
>
> Dim ws As Workspace
> Dim db As Database
> Dim strConnection As String
> Set ws = DBEngine.Workspaces(0)
> Let strConnection = "ODBC;DSN=hna;UID=;PWD="
> Set db = ws.OpenDatabase("", False, False, strConnection)
> Dim rs As Recordset
> Dim SQLStuff
> SQLStuff = "SELECT * FROM Bill"
> Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)
>
>I'm getting Run Time Error 3001, Invalid argument.
>
>Can anyone see what's invalid?
>
>Bernie
>Getting very frustrated and wondering why he can't figure this out anymore.
>
>

[Douglas J. Steele]

While I too would like to know what line's causing the problem, I'll hazard
a guess that it's the OpenRecordset line. If you were previously using ADO,
but now are using DAO, did you remove the reference to ADO?

Recordset is an object in both the DAO and ADO models. In the usual course
of events, the ADO reference will be higher up in sequence than the DAO
reference, so Access will choose the ADO recordset, rather than the DAO one
you want.

There are 2 possible solutions. The first is to remove the reference to ADO.
If you can't do that, then you need to "disambigulate" your reference as Dim
rs As DAO.Recordset (and you'd use Dim rs As ADODB.Recordset when you want
to be sure you get an ADO recordset).

A complete list of objects with the same names in the 2 models is:
Connection, Error, Errors, Field, Fields, Parameter, Parameters, Property,
Properties and Recordset

HTH

--

Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/


"Bernie Hunt" <bhunt@optonline.net> wrote in message
news:3a823d7b$1@news.devx.com...
> I gave up for now with ADO and am trying to get a DAO connection working.
It
> is apparently not my week.
>
> Dim ws As Workspace
> Dim db As Database
> Dim strConnection As String
> Set ws = DBEngine.Workspaces(0)
> Let strConnection = "ODBC;DSN=hna;UID=;PWD="
> Set db = ws.OpenDatabase("", False, False, strConnection)
> Dim rs As Recordset
> Dim SQLStuff
> SQLStuff = "SELECT * FROM Bill"
> Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)
>
> I'm getting Run Time Error 3001, Invalid argument.
>
> Can anyone see what's invalid?
>
> Bernie
> Getting very frustrated and wondering why he can't figure this out
anymore.
>
>

[Bernie Hunt]

Whoops, I new I left something out. The error is during the OpenRecordset
command.
I tired intering the DSN name in the database name field, but get the same
error when going to open the recordset.

Bernie

Paul Clement <UseAdddressAtEndofMessage@swspectrum.com> wrote in message
news:r2i58tk61n7lc431a8kcv17ah2u7o6qjs0@4ax.com...
> On Thu, 8 Feb 2001 01:26:14 -0500, "Bernie Hunt" <bhunt@optonline.net>
wrote:
>
> ¤ I gave up for now with ADO and am trying to get a DAO connection
working. It
> ¤ is apparently not my week.
> ¤
> ¤ Dim ws As Workspace
> ¤ Dim db As Database
> ¤ Dim strConnection As String
> ¤ Set ws = DBEngine.Workspaces(0)
> ¤ Let strConnection = "ODBC;DSN=hna;UID=;PWD="
> ¤ Set db = ws.OpenDatabase("", False, False, strConnection)
> ¤ Dim rs As Recordset
> ¤ Dim SQLStuff
> ¤ SQLStuff = "SELECT * FROM Bill"
> ¤ Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)
> ¤
> ¤ I'm getting Run Time Error 3001, Invalid argument.
> ¤
> ¤ Can anyone see what's invalid?
>
> Which line of code?
>
> Have you tried specifying the database name argument for OpenDatabase
instead of an empty string?
>
>
> Paul ~~~ pclement@ameritech.net
> Microsoft MVP (Visual Basic)

[Bernie Hunt]

Arthur,

Yes, it's breaking at the OpenRecordset command. I get the same result when
single stepping or running until the error.

Bernie

Arthur Wood <wooda@saic-trsc.com> wrote in message
news:3a82cbf8$1@news.devx.com...
>
> Bernie,
> What line is causing the error. Have you set a breakpoint in your code
> and then single-stepped through it?
>
>
> Arthur Wood
>
>
> "Bernie Hunt" <bhunt@optonline.net> wrote:
> >I gave up for now with ADO and am trying to get a DAO connection working.
> It
> >is apparently not my week.
> >
> > Dim ws As Workspace
> > Dim db As Database
> > Dim strConnection As String
> > Set ws = DBEngine.Workspaces(0)
> > Let strConnection = "ODBC;DSN=hna;UID=;PWD="
> > Set db = ws.OpenDatabase("", False, False, strConnection)
> > Dim rs As Recordset
> > Dim SQLStuff
> > SQLStuff = "SELECT * FROM Bill"
> > Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)
> >
> >I'm getting Run Time Error 3001, Invalid argument.
> >
> >Can anyone see what's invalid?
> >
> >Bernie
> >Getting very frustrated and wondering why he can't figure this out
anymore.
> >
> >
>

[Bernie Hunt]

Doug,

The break is on the OpenRecordset. I'm implemented your suggestions. I
verified that ADO is no longer selected under Project|References. The only
things selected now are:
Visual Basic for Applications
Visual Basic runtime objects and procedures
Visual Basic objects and procedures
OLE Automation
Microsoft DAO 3.51 Object Library
In that order.

The code only has this simple test function. I'll repost to include the
change in the reference in the Dim Recordset. The error is still the 3001 at
the "Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)" line.
The Remmed out version of that line produces the same 3001 error.

Sub OpenDB1()

Dim ws As Workspace
Dim db As Database
Dim strConnection As String
Set ws = DBEngine.Workspaces(0)
Let strConnection = "ODBC;DSN=hna;UID=;PWD="
Set db = ws.OpenDatabase("hna", False, False, strConnection)
Debug.Print "Database Connection open"
Dim rs As DAO.Recordset
Dim SQLStuff
SQLStuff = "SELECT * FROM Bill"
'Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic, 0,
dbOptimistic)
Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)
Debug.Print "Recordset open"
Dim i
i = 1
Do While Not rs.EOF
Debug.Print "Reading record " & i
i = i + 1
rs.MoveNext
Loop

End Sub


Thanks for the help!

Bernie

Douglas J. Steele <djsteele@idirect.com> wrote in message
news:3a832d28@news.devx.com...
> While I too would like to know what line's causing the problem, I'll
hazard
> a guess that it's the OpenRecordset line. If you were previously using
ADO,
> but now are using DAO, did you remove the reference to ADO?
>
> Recordset is an object in both the DAO and ADO models. In the usual course
> of events, the ADO reference will be higher up in sequence than the DAO
> reference, so Access will choose the ADO recordset, rather than the DAO
one
> you want.
>
> There are 2 possible solutions. The first is to remove the reference to
ADO.
> If you can't do that, then you need to "disambigulate" your reference as
Dim
> rs As DAO.Recordset (and you'd use Dim rs As ADODB.Recordset when you want
> to be sure you get an ADO recordset).
>
> A complete list of objects with the same names in the 2 models is:
> Connection, Error, Errors, Field, Fields, Parameter, Parameters, Property,
> Properties and Recordset
>
> HTH
>
> --
>
> Doug Steele, Microsoft Access MVP
> Beer, Wine and Database Programming. What could be better?
> Visit "Doug Steele's Beer and Programming Emporium"
> http://I.Am/DougSteele/
>
>
> "Bernie Hunt" <bhunt@optonline.net> wrote in message
> news:3a823d7b$1@news.devx.com...
> > I gave up for now with ADO and am trying to get a DAO connection
working.
> It
> > is apparently not my week.
> >
> > Dim ws As Workspace
> > Dim db As Database
> > Dim strConnection As String
> > Set ws = DBEngine.Workspaces(0)
> > Let strConnection = "ODBC;DSN=hna;UID=;PWD="
> > Set db = ws.OpenDatabase("", False, False, strConnection)
> > Dim rs As Recordset
> > Dim SQLStuff
> > SQLStuff = "SELECT * FROM Bill"
> > Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)
> >
> > I'm getting Run Time Error 3001, Invalid argument.
> >
> > Can anyone see what's invalid?
> >
> > Bernie
> > Getting very frustrated and wondering why he can't figure this out
> anymore.
> >
> >
>
>

[Douglas J. Steele]

Actually, looking a bit closer at your code, I don't believe your
OpenDatabase statement is correct.

The Help file indicates:

Set database = workspace.OpenDatabase (dbname, options, read-only, connect)

The OpenDatabase method syntax has these parts.

Part Description
database An object variable that represents the Database
object that you want to open.
workspace Optional. An object variable that represents the
existing Workspace object that will contain the
database. If you don't include a value for
workspace, OpenDatabase uses the default workspace.
dbname A String that is the name of an existing Microsoft Jet
database file, or the data source name (DSN) of an ODBC
data source. See the Name property for more information
about setting this value.
options Optional. A Variant that sets various options for the
database, as specified in Settings.
read-only Optional. A Variant (Boolean subtype) value that is True if
you want to open the database with read-only access, or
False
(default) if you want to open the database with
read/write access.
connect Optional. A Variant (String subtype) that specifies various
connection information, including passwords.

The first argument (dbname), which you've set to "", appears to be required,
and should be the DSN name.

False does not appear to be a valid option for the second argument for
ODBCDirect workspaces. Rather, it's supposed to be one of dbDriverNoPrompt,
dbDriverPrompt, dbDriverComplete or dbDriverCompleteRequired.

Since dbOpenDynamic is only valid for opening recordsets in ODBCDirect
workspaces, perhaps your invalid argument is occurring because it doesn't
consider db to be for an ODBCDirect workspace.

--

Doug Steele, Microsoft Access MVP
Beer, Wine and Database Programming. What could be better?
Visit "Doug Steele's Beer and Programming Emporium"
http://I.Am/DougSteele/


"Bernie Hunt" <bhunt@optonline.net> wrote in message
news:3a838b18$1@news.devx.com...
> Doug,
>
> The break is on the OpenRecordset. I'm implemented your suggestions. I
> verified that ADO is no longer selected under Project|References. The only
> things selected now are:
> Visual Basic for Applications
> Visual Basic runtime objects and procedures
> Visual Basic objects and procedures
> OLE Automation
> Microsoft DAO 3.51 Object Library
> In that order.
>
> The code only has this simple test function. I'll repost to include the
> change in the reference in the Dim Recordset. The error is still the 3001
at
> the "Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)" line.
> The Remmed out version of that line produces the same 3001 error.
>
> Sub OpenDB1()
>
> Dim ws As Workspace
> Dim db As Database
> Dim strConnection As String
> Set ws = DBEngine.Workspaces(0)
> Let strConnection = "ODBC;DSN=hna;UID=;PWD="
> Set db = ws.OpenDatabase("hna", False, False, strConnection)
> Debug.Print "Database Connection open"
> Dim rs As DAO.Recordset
> Dim SQLStuff
> SQLStuff = "SELECT * FROM Bill"
> 'Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic, 0,
> dbOptimistic)
> Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)
> Debug.Print "Recordset open"
> Dim i
> i = 1
> Do While Not rs.EOF
> Debug.Print "Reading record " & i
> i = i + 1
> rs.MoveNext
> Loop
>
> End Sub
>
>
> Thanks for the help!
>
> Bernie
>
> Douglas J. Steele <djsteele@idirect.com> wrote in message
> news:3a832d28@news.devx.com...
> > While I too would like to know what line's causing the problem, I'll
> hazard
> > a guess that it's the OpenRecordset line. If you were previously using
> ADO,
> > but now are using DAO, did you remove the reference to ADO?
> >
> > Recordset is an object in both the DAO and ADO models. In the usual
course
> > of events, the ADO reference will be higher up in sequence than the DAO
> > reference, so Access will choose the ADO recordset, rather than the DAO
> one
> > you want.
> >
> > There are 2 possible solutions. The first is to remove the reference to
> ADO.
> > If you can't do that, then you need to "disambigulate" your reference as
> Dim
> > rs As DAO.Recordset (and you'd use Dim rs As ADODB.Recordset when you
want
> > to be sure you get an ADO recordset).
> >
> > A complete list of objects with the same names in the 2 models is:
> > Connection, Error, Errors, Field, Fields, Parameter, Parameters,
Property,
> > Properties and Recordset
> >
> > HTH
> >
> > --
> >
> > Doug Steele, Microsoft Access MVP
> > Beer, Wine and Database Programming. What could be better?
> > Visit "Doug Steele's Beer and Programming Emporium"
> > http://I.Am/DougSteele/
> >
> >
> > "Bernie Hunt" <bhunt@optonline.net> wrote in message
> > news:3a823d7b$1@news.devx.com...
> > > I gave up for now with ADO and am trying to get a DAO connection
> working.
> > It
> > > is apparently not my week.
> > >
> > > Dim ws As Workspace
> > > Dim db As Database
> > > Dim strConnection As String
> > > Set ws = DBEngine.Workspaces(0)
> > > Let strConnection = "ODBC;DSN=hna;UID=;PWD="
> > > Set db = ws.OpenDatabase("", False, False, strConnection)
> > > Dim rs As Recordset
> > > Dim SQLStuff
> > > SQLStuff = "SELECT * FROM Bill"
> > > Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)
> > >
> > > I'm getting Run Time Error 3001, Invalid argument.
> > >
> > > Can anyone see what's invalid?
> > >
> > > Bernie
> > > Getting very frustrated and wondering why he can't figure this out
> > anymore.
> > >
> > >
> >
> >
>
>

[Ted McNeal]

Bernie,

The problem is that you are attempting to open an ODBC direct connection
in a Jet workspace (Ref: set ws = DBEngine(0)).

ODBC Direct bypasses the Jet engine and hence requires a separate instance
of the workspace object. The argument is invalid because a dynamic recordset
is not supported in a Jet workspace. Also, I suggest using the connection
object for ODBC Direct connections. Use of the database object in ODBC direct
workspaces is provided for backwards compatibility.

Set wsp = DBEngine.CreateWorkspace("", "", "", dbUseODBC)
Set con = wsp.OpenConnection("", dbDriverNoPrompt, False, strConn)

Ted McNeal


"Douglas J. Steele" <djsteele@idirect.com> wrote:
>Actually, looking a bit closer at your code, I don't believe your
>OpenDatabase statement is correct.
>
>The Help file indicates:
>
>Set database = workspace.OpenDatabase (dbname, options, read-only, connect)
>
>The OpenDatabase method syntax has these parts.
>
>Part Description
>database An object variable that represents the Database
> object that you want to open.
>workspace Optional. An object variable that represents the
> existing Workspace object that will contain the
> database. If you don't include a value for
> workspace, OpenDatabase uses the default workspace.
>dbname A String that is the name of an existing Microsoft Jet
> database file, or the data source name (DSN) of an ODBC
> data source. See the Name property for more information
> about setting this value.
>options Optional. A Variant that sets various options for the
> database, as specified in Settings.
>read-only Optional. A Variant (Boolean subtype) value that is True if
> you want to open the database with read-only access,
or
>False
> (default) if you want to open the database with
>read/write access.
>connect Optional. A Variant (String subtype) that specifies various
> connection information, including passwords.
>
>The first argument (dbname), which you've set to "", appears to be required,
>and should be the DSN name.
>
>False does not appear to be a valid option for the second argument for
>ODBCDirect workspaces. Rather, it's supposed to be one of dbDriverNoPrompt,
>dbDriverPrompt, dbDriverComplete or dbDriverCompleteRequired.
>
>Since dbOpenDynamic is only valid for opening recordsets in ODBCDirect
>workspaces, perhaps your invalid argument is occurring because it doesn't
>consider db to be for an ODBCDirect workspace.
>
>--
>
>Doug Steele, Microsoft Access MVP
>Beer, Wine and Database Programming. What could be better?
>Visit "Doug Steele's Beer and Programming Emporium"
>http://I.Am/DougSteele/
>
>
>"Bernie Hunt" <bhunt@optonline.net> wrote in message
>news:3a838b18$1@news.devx.com...
>> Doug,
>>
>> The break is on the OpenRecordset. I'm implemented your suggestions. I
>> verified that ADO is no longer selected under Project|References. The
only
>> things selected now are:
>> Visual Basic for Applications
>> Visual Basic runtime objects and procedures
>> Visual Basic objects and procedures
>> OLE Automation
>> Microsoft DAO 3.51 Object Library
>> In that order.
>>
>> The code only has this simple test function. I'll repost to include the
>> change in the reference in the Dim Recordset. The error is still the 3001
>at
>> the "Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)" line.
>> The Remmed out version of that line produces the same 3001 error.
>>
>> Sub OpenDB1()
>>
>> Dim ws As Workspace
>> Dim db As Database
>> Dim strConnection As String
>> Set ws = DBEngine.Workspaces(0)
>> Let strConnection = "ODBC;DSN=hna;UID=;PWD="
>> Set db = ws.OpenDatabase("hna", False, False, strConnection)
>> Debug.Print "Database Connection open"
>> Dim rs As DAO.Recordset
>> Dim SQLStuff
>> SQLStuff = "SELECT * FROM Bill"
>> 'Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic, 0,
>> dbOptimistic)
>> Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)
>> Debug.Print "Recordset open"
>> Dim i
>> i = 1
>> Do While Not rs.EOF
>> Debug.Print "Reading record " & i
>> i = i + 1
>> rs.MoveNext
>> Loop
>>
>> End Sub
>>
>>
>> Thanks for the help!
>>
>> Bernie
>>
>> Douglas J. Steele <djsteele@idirect.com> wrote in message
>> news:3a832d28@news.devx.com...
>> > While I too would like to know what line's causing the problem, I'll
>> hazard
>> > a guess that it's the OpenRecordset line. If you were previously using
>> ADO,
>> > but now are using DAO, did you remove the reference to ADO?
>> >
>> > Recordset is an object in both the DAO and ADO models. In the usual
>course
>> > of events, the ADO reference will be higher up in sequence than the
DAO
>> > reference, so Access will choose the ADO recordset, rather than the
DAO
>> one
>> > you want.
>> >
>> > There are 2 possible solutions. The first is to remove the reference
to
>> ADO.
>> > If you can't do that, then you need to "disambigulate" your reference
as
>> Dim
>> > rs As DAO.Recordset (and you'd use Dim rs As ADODB.Recordset when you
>want
>> > to be sure you get an ADO recordset).
>> >
>> > A complete list of objects with the same names in the 2 models is:
>> > Connection, Error, Errors, Field, Fields, Parameter, Parameters,
>Property,
>> > Properties and Recordset
>> >
>> > HTH
>> >
>> > --
>> >
>> > Doug Steele, Microsoft Access MVP
>> > Beer, Wine and Database Programming. What could be better?
>> > Visit "Doug Steele's Beer and Programming Emporium"
>> > http://I.Am/DougSteele/
>> >
>> >
>> > "Bernie Hunt" <bhunt@optonline.net> wrote in message
>> > news:3a823d7b$1@news.devx.com...
>> > > I gave up for now with ADO and am trying to get a DAO connection
>> working.
>> > It
>> > > is apparently not my week.
>> > >
>> > > Dim ws As Workspace
>> > > Dim db As Database
>> > > Dim strConnection As String
>> > > Set ws = DBEngine.Workspaces(0)
>> > > Let strConnection = "ODBC;DSN=hna;UID=;PWD="
>> > > Set db = ws.OpenDatabase("", False, False, strConnection)
>> > > Dim rs As Recordset
>> > > Dim SQLStuff
>> > > SQLStuff = "SELECT * FROM Bill"
>> > > Set rs = db.OpenRecordset("SELECT * FROM Bill", dbOpenDynamic)
>> > >
>> > > I'm getting Run Time Error 3001, Invalid argument.
>> > >
>> > > Can anyone see what's invalid?
>> > >
>> > > Bernie
>> > > Getting very frustrated and wondering why he can't figure this out
>> > anymore.
>> > >
>> > >
>> >
>> >
>>
>>
>
>