base access report on odbc recordset?

[jordanking]

hello,

I am trying to use a connection to an odbc mysql database in order to get a recordset and then use that recordset as the source for a report that is opened in Access 2003.

However I am getting a type mismatch error at the specified line:

Private Sub Report_Open(Cancel As Integer)
'establish error handling
On Error GoTo Err_Click

Dim conn As New ADODB.Connection
'connect to MySQL server using MySQL ODBC
Set conn = New ADODB.Connection
Dim rsUsers As New ADODB.Recordset
Dim usersSQL As String
Dim userRows As Long
Dim resRows As Long
Dim purchRows As Long

Dim i As Integer

'connect to the online regent MySQL database
'OPTION 3 sets myODBC to optimal use for access and vba
conn.Open "Driver={mySQL ODBC 3.51 Driver};" & _
           "Data Source Name=Erequest;" & _
           "Option=3;" & _
           "Server=*******;" & _
           "Database=********;" & _
           "Uid=*******;" & _
           "Pwd=****;"

'establish sql staements
usersSQL = "SELECT users.user_id, users.cust_id, users.username, users.first_name, users.family_name, users.admin_priv, users.comp_priv, customer.idsCustomerID, customer.lngzCompanyID, company.idsCompanyID, company.txtName, company.txtBranch" & _
           " FROM users LEFT JOIN customer ON users.cust_id =  customer.idsCustomerID LEFT JOIN company ON customer.lngzCompanyID = company.idsCompanyID"

'open and retireve record count from users table
rsUsers.Source = usersSQL
Set rsUsers.ActiveConnection = conn
rsUsers.CursorLocation = adUseClient
rsUsers.Open

'set recordset value of report
Me.RecordSource = rsUsers  '''''''''HERE IS THE ERROR''''''

'close recordset
rsUsers.Close

'close connection
conn.Close

Exit_Click:
    Exit Sub

Err_Click:
    i = MsgBox("The following Error Occured:" & Chr(13) & Chr(13) & "DESCRIPTION: " & Err.Description & Chr(13) & "NUMBER:" & Err.Number & Chr(13) & Chr(13) & "The current action will be canceled.", vbOKOnly + vbExclamation + vbApplicationModal + vbMsgBoxSetForeground, "Regent Database Error")
    Resume Exit_Click
    
End Sub

this specifies
Me.RecordSource = rsUsers
that the recordsource is the type mismatch culprit. Is it possible to do what I am trying?

I also posted this in the VB version 5 & 6 forum but i think that was the wrong spot. Sorry for the double post.

 

[jadams0173]

just a guess you can try

Reports!<reportname>.recordsource=rsusers

 

[PHV]

You met try this:
Set Me.RecordSet = rsUsers

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jordanking]

phv

I inserted your code and got the following error


This feature is not available in an MDB NUMBER: 2593