Passing a SQL String from Access to MySQL and Returning a Recordset 1

[TheFitz]

Hi All,

I'm trying to create a generic routine to pass a SQL statement to and return a recordset. This code works if I call a function within MySQL, however, it doesn't work with a SQL statement.

Any ideas?

Routine for reading the returned Recordset

Sub dftestRecordSet()
Dim strSQL As String, rstRecord As ADODB.Recordset


strSQL = "'SELECT PEO_ID FROM EBGSDT.tblPeople;'"

Set rstRecord = ReturnRST(strSQL)

rstRecord.MoveFirst
MsgBox rstRecord![PEO_FORENAME]

End Sub

Routine to return the recordset

Function ReturnRST(strSQL As String) As ADODB.Recordset

Const DB_CONNECT As String = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=EBGSDT;User=****; Password=****;Option=3;"

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim starttime As Date
Dim endtime As Date
Dim strstatus As String

starttime = Now()

Set cn = New ADODB.Connection

With cn
  .ConnectionString = DB_CONNECT
  .Open
End With

Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = cn
  .CommandText = strSQL
  .CommandType = adCmdStoredProc
  Set rst = .Execute
End With

Set ReturnRST = rst

End Function

A star for anyone who can help . . . .

Thanks


Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!

 

[dhookom]

I would try:

strSQL = "SELECT PEO_ID FROM EBGSDT.tblPeople;"

I'm also not sure if mysql requires the semi-colon.

Duane
Hook'D on Access
MS Access MVP

 

[TheFitz]

Thanks Duane for the info, I've solved it, with was a school boy error in my coding:

Function ReturnRST(strSQL As String) As ADODB.Recordset

Const DB_CONNECT As String = "Driver={MySQL ODBC 5.1 Driver};Server=127.0.0.1;Port=3306;Database=EBGSDT;User=****; Password=****;Option=3;"

Dim cn As ADODB.Connection
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim starttime As Date
Dim endtime As Date
Dim strstatus As String

starttime = Now()

Set cn = New ADODB.Connection

With cn
  .ConnectionString = DB_CONNECT
  .Open
End With

Set cmd = New ADODB.Command
With cmd
  .ActiveConnection = cn
  .CommandText = strSQL
  .CommandType = [b]adCmdStoredProc[/b]
  [b][COLOR=red].CommandType = adCmdText[/color][/b] [b][COLOR=green]'Should be this[/color][/b]
  Set rst = .Execute
End With

Set ReturnRST = rst

End Function

Thanks for your help anyway. Have a Star for luck.

Thanks

Fitz
Did you know, there are 10 types of people in this world:
* Those who understand binary
and
* Those who Don't!!