Visual Basic 6.0 / ADO / Using an Access Query
| Author |
Message |
|
Bria
#1 / 5
|
 Visual Basic 6.0 / ADO / Using an Access Query I have seen a *large* number of posts regarding my question, but nothing I've tried has worked, so I thought I'd try my own post. I am trying to access a query from an Access 2002 database. The query
returns results in Access, but the recordset returns 0 records in VB.
I've posted all the different line types I've tried. Thanks in advance
for any help.
---------------------------------------------------------------------------
Dim qryName As String
Set objCon = New ADODB.Connection
objCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & fileDatabase & ";Persist Security Info=False"
objCon.Open
Set objRS = New ADODB.Recordset
Try 1:
objRS.Open "SELECT * FROM [" & qryName & "];", objCon, adOpenKeyset,
adLockOptimistic 'Returns zero
Try 2:
objRS.Open qryName, objCon, adOpenKeyset, adLockOptimistic 'Returns
zero
Try 3:
objRS.Open qryName, objCon, adOpenKeyset, adCmdStoredProc 'Returns
zero
Try 4:
objRS.Open "SELECT * FROM " & qryName, objCon, adOpenKeyset,
adCmdStoredProc 'Returns zero
Try 5:
objRS.Open qryName, objCon, , , adCmdStoredProc 'Returns -1 By far my
FAVORITE one yet! :-)
|
| Mon, 06 Sep 2004 02:27:11 GMT |
|
 |
|
joha
#2 / 5
|
Visual Basic 6.0 / ADO / Using an Access Query This code has always worked for me, in fact I use it for connecting to any kind of database - obviously your Provider string needs to be correct. Definition:
strSQL as string
rsRecordSet as adodb.recordset
dbConnection as adodb.connection
dbconnection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
Source=" & dbName & ";Persist Security Info=False"
dbconnection.Open
strSQL="Select * from [" & qryName & "]"
rsRecordSet.open strsql,dbconnection,ANY OPTION,ANY OPTION
rsRecordSet.close
set rsrecordset=nothing
set dbconnection-nothing
'Remember to always close your recordsets after you are done using them and
cancel the connection
Good Luck
Quote: > I have seen a *large* number of posts regarding my question, but > nothing I've tried has worked, so I thought I'd try my own post. > I am trying to access a query from an Access 2002 database. The query
> returns results in Access, but the recordset returns 0 records in VB.
> I've posted all the different line types I've tried. Thanks in advance
> for any help.
> --------------------------------------------------------------------------
-
> Dim qryName As String
> Set objCon = New ADODB.Connection
> objCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=" & fileDatabase & ";Persist Security Info=False"
> objCon.Open
> Set objRS = New ADODB.Recordset
> Try 1:
> objRS.Open "SELECT * FROM [" & qryName & "];", objCon, adOpenKeyset,
> adLockOptimistic 'Returns zero
> Try 2:
> objRS.Open qryName, objCon, adOpenKeyset, adLockOptimistic 'Returns
> zero
> Try 3:
> objRS.Open qryName, objCon, adOpenKeyset, adCmdStoredProc 'Returns
> zero
> Try 4:
> objRS.Open "SELECT * FROM " & qryName, objCon, adOpenKeyset,
> adCmdStoredProc 'Returns zero
> Try 5:
> objRS.Open qryName, objCon, , , adCmdStoredProc 'Returns -1 By far my
> FAVORITE one yet! :-)
|
| Wed, 08 Sep 2004 05:48:23 GMT |
|
|
|
Harry Strybo
#3 / 5
|
Visual Basic 6.0 / ADO / Using an Access Query
Quote: > I have seen a *large* number of posts regarding my question, but > nothing I've tried has worked, so I thought I'd try my own post. > I am trying to access a query from an Access 2002 database. The query
> returns results in Access, but the recordset returns 0 records in VB.
> I've posted all the different line types I've tried. Thanks in advance
> for any help.
> --------------------------------------------------------------------------
-
> Dim qryName As String
> Set objCon = New ADODB.Connection
> objCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=" & fileDatabase & ";Persist Security Info=False"
> objCon.Open
> Set objRS = New ADODB.Recordset
> Try 1:
> objRS.Open "SELECT * FROM [" & qryName & "];", objCon, adOpenKeyset,
> adLockOptimistic 'Returns zero
> Try 2:
> objRS.Open qryName, objCon, adOpenKeyset, adLockOptimistic 'Returns
> zero
> Try 3:
> objRS.Open qryName, objCon, adOpenKeyset, adCmdStoredProc 'Returns
> zero
> Try 4:
> objRS.Open "SELECT * FROM " & qryName, objCon, adOpenKeyset,
> adCmdStoredProc 'Returns zero
> Try 5:
> objRS.Open qryName, objCon, , , adCmdStoredProc 'Returns -1 By far my
> FAVORITE one yet! :-)
Try: objRS.Open "EXEC " & qryName, objCon, adOpenKeyset,
adLockOptimistic 'Returns zero
|
| Fri, 10 Sep 2004 07:33:20 GMT |
|
|
|
willi
#4 / 5
|
Visual Basic 6.0 / ADO / Using an Access Query try '" & qryName & "' instead of [" & qryName & "] mention the extra ' willie
Quote: > I have seen a *large* number of posts regarding my question, but > nothing I've tried has worked, so I thought I'd try my own post. > I am trying to access a query from an Access 2002 database. The query
> returns results in Access, but the recordset returns 0 records in VB.
> I've posted all the different line types I've tried. Thanks in advance
> for any help.
> --------------------------------------------------------------------------
-
> Dim qryName As String
> Set objCon = New ADODB.Connection
> objCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data
> Source=" & fileDatabase & ";Persist Security Info=False"
> objCon.Open
> Set objRS = New ADODB.Recordset
> Try 1:
> objRS.Open "SELECT * FROM [" & qryName & "];", objCon, adOpenKeyset,
> adLockOptimistic 'Returns zero
> Try 2:
> objRS.Open qryName, objCon, adOpenKeyset, adLockOptimistic 'Returns
> zero
> Try 3:
> objRS.Open qryName, objCon, adOpenKeyset, adCmdStoredProc 'Returns
> zero
> Try 4:
> objRS.Open "SELECT * FROM " & qryName, objCon, adOpenKeyset,
> adCmdStoredProc 'Returns zero
> Try 5:
> objRS.Open qryName, objCon, , , adCmdStoredProc 'Returns -1 By far my
> FAVORITE one yet! :-)
|
| Mon, 13 Sep 2004 01:19:20 GMT |
|
|
|
Joe
#5 / 5
|
Visual Basic 6.0 / ADO / Using an Access Query Quote:
> I have seen a *large* number of posts regarding my question, but > nothing I've tried has worked, so I thought I'd try my own post. > I am trying to access a query from an Access 2002 database. The query
> returns results in Access, but the recordset returns 0 records in VB.
> I've posted all the different line types I've tried. Thanks in advance
> for any help.
RecordCount might not know how many records there are until you actually visit them. What do the BOF and EOF properties say? --
Joe Foster <mailto:jlfoster%40znet.com> Sign the Check! <http://www.xenu.net/>
WARNING: I cannot be held responsible for the above They're coming to
because my cats have apparently learned to type. take me away, ha ha!
|
| Mon, 13 Sep 2004 02:36:22 GMT |
|
| |
|
