Putting standalone query results into ADO recordset (VBA)

[BennyLinton]

I am trying to pass the results of a query into a ADO recordset. This VBA code executes fine but does not yield the same results as a standalone query. How can I recode this so "Set rs1 = results of query..."? I know how to write the standalone query but not how to make it the recoset... thanks!

Here is the current VBA:

Set rs1 = con.Execute("Select c.peopleID, c.certificationID, c.certificationExpireDate, p.useCredits, Datepart(M,c.certificationExpireDate) From People p " & _
"INNER JOIN certs c ON p.peopleID = c.peopleID Where c.statusID = 1 and " & _
"Datepart(M,c.certificationExpireDate) = " & bmonth & " " & _
"Order by c.peopleID")

 

[namliam]

How does it not yield the same result? There should not be any difference between this and a query object.

 

[BennyLinton]

It should but it doesn't, so I wanted to use this stand-alone query that does return the correct records (month is hardcoded). this will give me some clues if I can get my VBA to set re1 to the results:

Select c.peopleID, c.certificationID, c.certificationExpireDate, p.useCredits, Datepart("m",c.certificationExpireDate) From dbo_People p
INNER JOIN dbo_certs c ON p.peopleID = c.peopleID Where c.statusID = 1 and
Datepart("m",c.certificationExpireDate) = 8
Order by c.peopleID

 

[BennyLinton]

I tried to use: Set rs1 = db.OpenRecordset("qryMonthlyBilling")
But I got a type mismatch error

 

[Simon_MT]

Can I make a suggestion. Create your Query as a Query and then qualify it using VBA. Then test your data and then put the Query statement into VBA. There is something that Access does that appears to optimise the Queries.

You can also declare the Sql Statement as a string and create a message box to test the SQL Statement.

Simon

 

[BennyLinton]

I wrote the query as a Query and it ran fine. I pasted the exact SQL below into the VBA:

SELECT c.peopleID, c.certificationID, c.certificationExpireDate, p.useCredits, Datepart("m",c.certificationExpireDate)
FROM dbo_People AS p INNER JOIN dbo_certs AS c ON p.peopleID = c.peopleID
WHERE c.statusID = 1 and
Datepart("m",c.certificationExpireDate) = 8
ORDER BY c.peopleID;

VBA did not like the double quotes "m", and did not recognize the tables dbo_People and dbo_certs, it wanted People and Certs as the names of the tables.

I tried both version of my query defined as a string value, then qrote this into the VBA code: Set rs = db.OpenRecordset(sBilling) and it still gives the same data (still incorrect count) and the same syntax errors when I try to use the SQL code.

 

[Galaxiom]

I tried to use: Set rs1 = db.OpenRecordset("qryMonthlyBilling")
But I got a type mismatch error

That is because rs1 is Dimmed as ADODB and you have tried to Set it with a DAO recordset.

I assume you are trying to open a recordset on a connection to a server. You need to use the schema table names from the server, not the Access translations used for linked tables.

Change dbo_People to dbo.People and dbo_Certs to dbo.Certs

 

[BennyLinton]

I changed that to dbo.People, etc., but the record count is still readically different... the commented out below is the successfful Query returning 1,238 records. When I place it into VBA I get 114 records:

'SELECT c.peopleID, c.certificationID, c.certificationExpireDate, p.useCredits, Datepart("m",c.certificationExpireDate)
'FROM dbo_People AS p INNER JOIN dbo_certs AS c ON p.peopleID = c.peopleID
'WHERE c.statusID = 1 and
'DatePart("m", c.certificationExpireDate) = 8
'ORDER BY c.peopleID;


Set rs1 = con.Execute("SELECT c.peopleID, c.certificationID, c.certificationExpireDate, p.useCredits, Datepart(m,c.certificationExpireDate) " & _
"FROM dbo.People AS p INNER JOIN dbo.certs AS c ON p.peopleID = c.peopleID " & _
"WHERE c.statusID = 1 and " & _
"DatePart(m, c.certificationExpireDate) = 8 " & _
"ORDER BY c.peopleID")

 

[Galaxiom]

DatePart first parameter should be a capital M for Month. Otherwise it is testing for minute = 8.

BTW Testing for a month by processing every record is a very slow technique. Better to test for a date range so the index on the date field can be used.

 

[BennyLinton]

I changed that to an uppercase M and there are no errors but I still get 114 records only and 1,238 from the standalone query.

 

[BennyLinton]

Rather than using the embedded SQl in VBA is there a way to write the below to pull directly from the successfully running stand-alone query?

Set rs1 = con.Execute("SELECT c.peopleID, c.certificationID, c.certificationExpireDate, p.useCredits, Datepart(M,c.certificationExpireDate) " & _
"FROM dbo.People AS p INNER JOIN dbo.certs AS c ON p.peopleID = c.peopleID " & _
"WHERE c.statusID = 1 and " & _
"DatePart(M, c.certificationExpireDate) = 8 " & _
"ORDER BY c.peopleID")
:banghead:

 

[Minty]

Can you post up a sample database with some sample data that demonstrates the issue ?

 

[BennyLinton]

I would take some effort, or some explaining on how I could easily do it. It is an Access front end with a SQL Server backend connect via ODBC. It is using adodb which I'm not the greatest at understanding to start with. It is a very complex solution.

I just wish there were a way of taking a standalone query that works perfectly and calling it to fill a recordset.

pseudo-code: rs1 = results of opening "qryBilling"

 

[Minty]

Can you not simply link to the tables ? (Stating the really obvious)

 

[BennyLinton]

There is for sure a better way of doing this using adodb, but I tried this using some DAO and now it is working. Thanks for helping me sort through it and many thanks to anyone that can give advice on the DAO - ADODB mix, but for now the monkey is off my back:

strCUser = CurrentUserName
Dim con As adodb.Connection
Dim cmd As adodb.Command
'Dim rs1 As adodb.Recordset
Dim rs2 As adodb.Recordset
Dim maintFee As Integer
Set con = New adodb.Connection
Set cmd = New adodb.Command
con.ConnectionString = CONN_STRING
con.Open
cmd.ActiveConnection = con
Dim strSQL As String

strCUser = CurrentUserName

Dim rs1 As DAO.Recordset

Set db = CurrentDb()

Set rs1 = db.OpenRecordset("qryMonthlyBilling")

 

[Galaxiom]

Are you completely sure of the correct number of records? Maybe you doubting the wrong query.

One big mistake when linking a table to Access via ODBC is failing to specify an appropriate set of fields as a Primary Key when asked during the import. This dialog is raised if the PK is not specified on the server table. Get this wrong and Access will duplicate records.

It could explain why your query against the linked table is returning a lot more records than the command direct to the server.

 

[BennyLinton]

My problem now is that the month was hardcoded in the standalone query and I can't substitute a reference to the form's month entry so I'm having to figure out why the two queries are yielding dramatically different record counts:

Set rs1 = con.Execute("Select c.peopleID, c.certificationID, c.certificationExpireDate, p.useCredits, Datepart(M,c.certificationDate) " & _
"From People p INNER JOIN certs c ON p.peopleID = c.peopleID " & _
"Where c.StatusID = 1 and " & _
"Datepart(M,c.certificationDate) = " & bmonth & " " & _
"Order by p.peopleID")


'SELECT c.peopleID, c.certificationID, c.certificationExpireDate, p.useCredits, Datepart("m",c.certificationExpireDate)
'FROM dbo_People AS p INNER JOIN dbo_certs AS c ON p.peopleID = c.peopleID
'WHERE c.statusID = 1 and
'DatePart("m", c.certificationExpireDate) = 8
'ORDER BY c.peopleID;

 

[BennyLinton]

Are you completely sure of the correct number of records? Maybe you doubting the wrong query.

One big mistake when linking a table to Access via ODBC is failing to specify an appropriate set of fields as a Primary Key when asked during the import. This dialog is raised if the PK is not specified on the server table. Get this wrong and Access will duplicate records.

It could explain why your query against the linked table is returning a lot more records than the command direct to the server.

Both queries are hitting on the ODBC linked table and I can write a similar query in SQL Server that returns the correct number of records.