Access 03 & MySQL

J

JohnPapa

Registered User.
Local time
Today, 21:20
Joined
Aug 15, 2010
Messages
1,117
I would like to use Access 03 fe and MySQL be. I can connect when I manually create an ODBC connection. Can I use ADO to create a DSN connection in ADO to create the connection on the fly?

I found the following code somewhere on thiw site. I tried to create the DSN with the following command,

blnDum = CreateDSN("localhost", "C:\ProgramData\MySQL\MySQL Server 5.5\data\jp2", "root", "1234")

The db can be found at
C:\ProgramData\MySQL\MySQL Server 5.5\data\jp2

If can automatically create the DSN with ADO (within Access 03) is my syntax OK? I put a breakpoint in Public Function CreateDSN and variable CreateDSN is set to false.



Code:
Option Compare Database
Option Explicit
Public g_sServerName As String
Public g_sDatabaseName As String
Public g_sUID As String
Public g_sPWD As String
'Constant Declaration
Private Const ODBC_ADD_DSN = 1 ' Add data source
Private Const ODBC_ADD_SYS_DSN = 4 ' Add System DSN
Private Const ODBC_CONFIG_DSN = 2 ' Configure (edit) data source
Private Const ODBC_REMOVE_DSN = 3 ' Remove data source
Private Const vbAPINull As Long = 0 ' NULL Pointer
Public Const DSNName = "MySQLExportDSN"
'Function Declare
#If Win32 Then
Private Declare Function SQLConfigDataSource Lib "ODBCCP32.DLL" (ByVal hwndParent As Long, ByVal fRequest As Long, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Long
#Else
Private Declare Function SQLConfigDataSource Lib "ODBCINST.DLL" (ByVal hwndParent As Integer, ByVal fRequest As Integer, ByVal lpszDriver As String, ByVal lpszAttributes As String) As Integer
#End If
Private Declare Function SQLGetInstalledDriver Lib "ODBCCP32.DLL" (ByVal lDrvList As String, ByVal lpszDriver As Long, ByVal lpszAttributes As Long) As Long
Public Function CreateDSN(ByVal sServerName_IN As String, ByVal sDSNDBName_IN As String, ByVal sUserName_IN As String, ByVal sPWD_IN As String) As Boolean
On Error GoTo errHandlerSection

#If Win32 Then
Dim lRet As Long
#Else
Dim intRet As Integer
#End If

Dim strDriver As String
Dim strAttributes As String

Dim sDriverList As String
Dim bRetValue As Boolean

' bRetValue = SQLGetInstalledDriver(sDriverList, Len(sDriverList), Len(sDriverList))

strDriver = ""
strAttributes = ""

strDriver = "MySQL"
strAttributes = "Server=" & sServerName_IN
strAttributes = strAttributes & ";Database=" & sDSNDBName_IN & ";DSN=" & DSNName & ";uid=" & sUserName_IN & ";Password=" & sPWD_IN

lRet = SQLConfigDataSource(vbAPINull, ODBC_ADD_DSN, strDriver, strAttributes)

If lRet = 0 Then
CreateDSN = False
Else
CreateDSN = True
End If

Exit Function

errHandlerSection:
MsgBox "Cannot create DSN" & vbCrLf & "Error description : " & Err.Description, vbCritical, "Internal Error"
CreateDSN = False
End Function
Public Function RemoveDSN() As Boolean
On Error GoTo errHandlerSection

#If Win32 Then
Dim lRet As Long
#Else
Dim intRet As Integer
#End If

Dim strDriver As String
Dim strAttributes As String

Dim sDriverList As String
Dim bRetValue As Boolean

lRet = SQLConfigDataSource(vbAPINull, ODBC_REMOVE_DSN, "MySQL", DSNName)

If lRet = 0 Then
RemoveDSN = False
Else
RemoveDSN = True
End If

Exit Function

errHandlerSection:
MsgBox "Cannot delete DSN" & vbCrLf & "Error description : " & Err.Description, vbCritical, "Internal Error"
RemoveDSN = False
End Function
Code:
 
JohnPapa said:
Can I use ADO to create a DSN connection in ADO to create the connection on the fly?
Click to expand...

Yes!! :D

ADO connectivity begins with the ADO.Connection object. That expects a DSN string to be provided to the Open even of that object.

Code: 
  Set adoConn = New ADODB.Connection
  adoConn.Open strDBConnectionString
So just build (somehow) an appropriate connection string and pass it to the Open event.

Then use that ADO.Connection object as you work with other ADO objects, and all will work very well.
 
Michael,

Thanks for your reply. Is the code which I included above of any use?

Can you please provide a more specific example. I seem to have some problem with defining the params.

The db can be found at
C:\ProgramData\MySQL\MySQL Server 5.5\data\jp2

The Server Name = "localhost"

The user name = "root"

The Password = "1234"

Thanks
 
Last edited:
JohnPapa said:
Is the code which I included above of any use?
Click to expand...

Looks complex compared to the DSN'less code I use. I do not have any code dealing with specific DLL files, as one example.

Seriously... just search around for a DSN string that is correct to connect to your MySQL box, even hard code the entire string just to see if you can get it working. Once working, then consider building the string via concatenation so that server name / database name / credentials may be separate attributes being looked up.
 
Michael, I forgot to say that I had a look at MySQL for the first time yesterday. As I mentioned, I manually created a Machine Data Source DSN and created table links in an empty Access 03 db.
You mention that it is possible to create the DSN on the fly.

If you have a concrete example please include, as it would help immensely.

THis may be a stupid question, but how do you link to the MySQL tables if no DSN exists and is created on the fly.
Thanks, John
 
Here is a link directly to the official MySQL page about such a concept...

http://dev.mysql.com/doc/refman/5.0/en/connector-odbc-configuration-connection-without-dsn.html

ADO objects do not use linked tables.

DAO objects do use linked tables. For Linked Tables I use an ADOX object to update the connection string of linked table objects. Code snipped as follows:

Code: 
  Dim adoCat As New ADOX.Catalog
  Dim adoTbl As New ADOX.Table

  'Define attachment to FE database
  Set adoCat = New ADOX.Catalog
  Set adoCat.ActiveConnection = CurrentProject.Connection
  Set adoTbl.ParentCatalog = adoCat

  'Refresh Linked Tables
  For Each adoTbl In adoCat.Tables
    If adoTbl.Type = "PASS-THROUGH" Then
      adoTbl.Properties("Jet OLEDB:Link Provider String") = MSysObjectsConnectString
    End If
  Next

  'Good return code
  UpdateConnectionsBasedOnTypePassThrough = True

Exit_UpdateConnectionsBasedOnTypePassThrough:
  'Clean up the connection to the database
  Set adoTbl = Nothing
  Set adoCat = Nothing
Note that the connection string in this case is slightly different. "And how did I successfully arrive at said differences...???" I can not remember now.

It looks to me the main difference is if the string needs to include "ODBC;" or not.
 
Michael,

I am having some problem in creating an ADO.Connection object. I try to access db "sakila", which comes with default installation.

Dim Conn1 As New ADODB.Connection
Conn1.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
"SERVER=localhost;" & _
"DATABASE=sakila;" & _
"USER=root;" & _
"PASSWORD=1234;" & _
"OPTION=3;"

I get Run-time error '2147467259 (80004005)':
[Microsoft][odbc Driver Manager] Data source name not found and no default driver specified


Any ideas?
 
JohnPapa said:
Michael,

I am having some problem in creating an ADO.Connection object.
Code: 
    Conn1.Open "DRIVER={MySQL ODBC 3.51 Driver};" & _
Click to expand...

Is that the EXACT name of the driver? Does it have {} brackets? Must be exactly the same name as is in Control Panel \ ODBC.
 
Many thanks Michael,
Driver name is MySQL ODBC 5.1 Driver. Works OK now.
I am investigating the table relink part now. My previous encounter with ADOX was not good. I hope this time it is better!
 
Hi Michael,

In summarizing what we have so far, we can connect using ADO


Code: 
Dim Conn1 As New ADODB.Connection
Conn1.Open "DRIVER=MySQL ODBC 5.1 Driver;" & _
"SERVER=localhost;" & _
"DATABASE=sakila;" & _
"USER=root;" & _
"PASSWORD=1234;" & _
"OPTION=3;"


I now need to loop through the linked tables and update the tables. I used Michael's code


Code: 
'Refresh Linked Tables
For Each adoTbl In adoCat.Tables
If adoTbl.Type = "PASS-THROUGH" Then
adoTbl.Properties("Jet OLEDB:Link Provider String") = MSysObjectsConnectString
End If
Next


It fails to compile because it does not recognise MSysObjectsConnectString, but if that like is commented out
then the code sucessfully loops through all tables.

If I hover the cursor on any of the linked tables I receive
"ODBC;DSN=sample-MySQL;TABLE=actor" where "sample-MySQL" = the name of the DSN and "actor" is the name of the table

I am using Access 03. Does anyone know what the linked table properties should be set to? SHould it be something like

adoTbl.Properties("Jet OLEDB:Link Provider String") = "ODBC;DSN=sample-MySQL;TABLE=" & adoTbl.Name

Many thanks
 
JohnPapa said:
It fails to compile because it does not recognise MSysObjectsConnectString
Click to expand...

The code I use to build the various strings is as follows:

Code: 
Public Property Get ODBCConnectString() As String

  'LOC for a DSN'less connection
  ODBCConnectString = "ODBC;Driver=SQL Server;Server=" & dbServer & ";Database=" & dbDatabase & ";UID=" & dbUID & ";PWD=" & dbPWD & ";"
End Property

Public Property Get MSysObjectsConnectString() As String

  'LOC for a DSN'less connection
  MSysObjectsConnectString = "Driver=SQL Server;Server=" & dbServer & ";Database=" & dbDatabase & ";Description=Fandango Development;UID=" & dbUID & ";PWD=" & dbPWD & ";APP=2007 Microsoft Office system;"
End Property
So, the ADOX code is using the MSysObjectsConnectString form of the string, not the ODBC one.

Back when I coded that, somewhere I got the syntax, copied it to the VBA code, and inserted the variable values. Now I do not recall where I found the connect string originally. And I recall for these Linked Table objects, at first I tried to directly update the string in the MSysObjects table, and was not allowed to do so. Then I arrived at the ADOX method of updating. Perhaps I arrived at the syntax from the MSysObjects table.
 
Many thanks Michael,

I include below the code (params are hardcoded) which appears to work.

Once again thanks for your assistance.
John

Code: 
Private Sub Form_Open(Cancel As Integer)
  Dim adoCat As New ADOX.Catalog
  Dim adoTbl As New ADOX.Table
  'Define attachment to FE database
  Set adoCat = New ADOX.Catalog
  Set adoCat.ActiveConnection = CurrentProject.Connection
  Set adoTbl.ParentCatalog = adoCat
  'Refresh Linked Tables
  For Each adoTbl In adoCat.Tables
    If adoTbl.Type = "PASS-THROUGH" Then
      adoTbl.Properties("Jet OLEDB:Link Provider String") = MSysObjectsConnectString
    End If
  Next
  'Clean up the connection to the database
  Set adoTbl = Nothing
  Set adoCat = Nothing
End Sub

Code: 
Public Property Get MSysObjectsConnectString() As String
  MSysObjectsConnectString = "Driver=MySQL ODBC 5.1 Driver;Server=localhost;Database=sakila;UID=root;PWD=1234;"
End Property
 
My pleasure, JohnPapa. I am pleased you arrived at success! :)
 
I created stored procedure

Code: 
CREATE [EMAIL="DEFINER=%60root%60@%60localhost"]DEFINER=`root`@`localhost[/EMAIL]` PROCEDURE `procJP3`()
BEGIN
        SELECT * FROM `actor` WHERE (`actor`.`first_name` LIKE 'A%');
END

which I can call from Access by a pass-trhough query

Code: 
CALL procjp3;

I would like 2 things:
1) Be able to pass the parameters, something like
Code: 
CREATE [EMAIL="DEFINER=%60root%60@%60localhost"][COLOR=#0066cc]DEFINER=`root`@`localhost[/COLOR][/EMAIL]` PROCEDURE `procJP3`(IN strParam VARCHAR(45))
BEGIN
SELECT * FROM `actor` WHERE (`actor`.`first_name` LIKE '" & strParam & "'%');
END

where strParam = "A"

2)Be able to use ADO achieve the above. When I use the following code, rst holds the required records. I would like to be able to pass the required parameters and have the result appear on a form.

Code: 
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim cmd As ADODB.Command
Dim strConnect As String
strConnect = "Driver=MySQL ODBC 5.1 Driver;SERVER=localhost;UID=root;PWD=1234;DATABASE=sakila;PORT=3306"
Set cnn = New ADODB.Connection
cnn.Open strConnect
Set cmd = New ADODB.Command
cmd.ActiveConnection = cnn
cmd.CommandText = "procjp3"
cmd.CommandType = adCmdStoredProc
Set rst = cmd.Execute("procjp3")
 
'*** rst holds required records
 
Set rst = Nothing
Set cnn = Nothing
Set cmd = Nothing
 
Stored Procedures - Excellent topic!!! :D

Driving SP's with DAO objects:

Code: 
    strSQLbe = "SET NOCOUNT ON; " & _
               "DECLARE @projectid smallint; " & _
               "SET @projectid = " & ObjProjectsTbl.id & "; " & _
               "EXEC dbo.clsObjProductsTbl_RefreshLocalTmpTbl_All @projectid;"
I use nested DAO.QueryDef objects. The inner object is configured in Pass-Through mode (SQL example above) and the outer DAO.QueryDef runs SQL in the FE DB to INSERT all of the records from the inner DAO.QueryDef object. Sample of that SQL here (the outer wrapper of the inner query just above...

Code: 
    strSQLfe = "INSERT INTO " & strFETempTableName & " (id,authid,authusername,logtimestamp,projectid,title,productnumber,bomcad) " & _
               "SELECT t.id,t.authid,t.authusername,t.logtimestamp,t.projectid,t.title,t.productnumber,t.bomcad " & _
               "FROM"
And I simply append the name of the INNER DAO.QueryDef to the OUTER query after the FROM keyword, thus the reason it abruptly ends thusly.

Example of DAO.QueryDef objects downloading records from a SQL BE DB via Pass-Through query and populating a FE temp table with them
http://www.access-programmers.co.uk/forums/showthread.php?p=1119605&posted=1#post1119605

Now, my favorite way to execute SP's: ADO objects!!

Code: 
  Dim adoCMD As ADODB.Command
  Dim adoRS As ADODB.Recordset

  'Define attachment to database table specifics and execute commands via With block
  Set adoCMD = New ADODB.Command
  With adoCMD
    .ActiveConnection = ObjBEDBConnection.getADODBConnectionObj()
    .CommandText = "clsObjProductsTbl_LocateProductByID"
    .CommandType = adCmdStoredProc
    .Parameters.Refresh
    .Parameters("@id").Value = Me.id

    Set adoRS = .Execute()
  End With

  With adoRS
    'Was no record found?
    If .BOF Or .EOF Then
      'Clear the class attributes
      Me.Clear
      LocateProductByID = False
    Else
      'Fetch the values found
      Me.id = Nz(adoRS!id, 0)
      Me.authid = Nz(adoRS!authid, 0)
      'Field resulting from the join to auth to look up the username for field authid
      Me.authusername = Nz(adoRS!authusername, vbNullString)
      Me.logtimestamp = Nz(adoRS!logtimestamp, vbNullString)
      Me.projectid = Nz(adoRS!projectid, 0)
      Me.title = Nz(adoRS!title, vbNullString)
      Me.productnumber = Nz(adoRS!productnumber, vbNullString)
      Me.bomcad = Nz(adoRS!bomcad, vbNullString)
      'Set a good return code
      LocateProductByID = True
    End If

    'Close the database table
    .Close
  End With

Exit_LocateProductByID:
  'Clean up the connection to the database
  Set adoCMD = Nothing
  Set adoRS = Nothing
The call to .Parameters.Refresh automatically propagates ADO.Paramenters objects for the SP based on the actual Parameters list of the SP. ADO guesses wrong in a few cases, such as for date and VarChar(MAX) instances. I merely fix up the Parameters datatype before assigning the value to those Parameters objects. It is quite obvious if it does not guess correctly, as VBA crashes! :rolleyes:

Best wishes!!!
 
Michael many thanks for your help so far,

If you can send me an address to my email john@kosmosbusiness.com, I would be more than happy to send you a copy of my latest book on Project Management.

Back to business!!. I modified your code as follows and I receive "Run-time error '3265:' "Item cannot be found in the collection corresponding to the requested name or ordinal" at
.Parameters("@first_name").Value = "ALEC"


Code: 
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strConnect As String
strConnect = "Driver=MySQL ODBC 5.1 Driver;SERVER=localhost;UID=root;PWD=1234;DATABASE=sakila;PORT=3306"
Set cnn = New ADODB.Connection
cnn.Open strConnect
Set cmd = New ADODB.Command
With cmd
   .ActiveConnection = cnn
   .CommandText = "procjp3"
   .CommandType = adCmdStoredProc
   .Parameters.Refresh
   .Parameters("@first_name").Value = "ALEC"
   Set rst = .Execute("procjp3")
End With
Dim blnDum As Boolean
With rst
    'Was no record found?
    If .BOF Or .EOF Then
      'Clear the class attributes
      'Me.Clear
      blnDum = False
    Else
      'Fetch the values found
      Me.first_name = Nz(rst!first_name, 0)
      'Set a good return code
      blnDum = True
    End If
    'Close the database table
    .Close
End With
Set rst = Nothing
Set cnn = Nothing
Set cmd = Nothing

SP procJP3 is a follows:

Code: 
DELIMITER $$
USE `sakila`$$
DROP PROCEDURE IF EXISTS `procJP3`$$
CREATE [EMAIL="DEFINER=%60root%60@%60localhost"]DEFINER=`root`@`localhost[/EMAIL]` PROCEDURE `procJP3`(IN strParam VARCHAR(45))
BEGIN
        SELECT * FROM `actor` WHERE (`actor`.`first_name` = strParam);
    END$$
DELIMITER ;

and it executes correctly as a pass-through query
Code: 
CALL procjp3("ALEC");

Any ideas?

If there are more than one records returned, can these populate a tabular form?
 
JohnPapa said:
I receive "Run-time error '3265:' "Item cannot be found in the collection corresponding to the requested name or ordinal" at
.Parameters("@first_name").Value = "ALEC"


Code: 
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strConnect As String
strConnect = "Driver=MySQL ODBC 5.1 Driver;SERVER=localhost;UID=root;PWD=1234;DATABASE=sakila;PORT=3306"
Set cnn = New ADODB.Connection
cnn.Open strConnect
[COLOR=Blue]Set cmd = New ADODB.Command[/COLOR]
With [COLOR=Red]cmd[/COLOR]
   .ActiveConnection = cnn
   .CommandText = "procjp3"
   .CommandType = adCmdStoredProc
   [COLOR=Red].Parameters.Refresh[/COLOR]
   .Parameters("@first_name").Value = "ALEC"
   Set rst = .Execute([COLOR=Lime]"procjp3"[/COLOR])
End With
Any ideas?
Click to expand...

Sure!
1) Set a break point on the blue LOC
2) Put a watch on cmd
3) Step forward until you execute the .Parameters.Refresh
4) Inspect the contents of the cmd object in the watch window, drill down to Parameters, and step through each numbered Parameter instance searching for the correct name of the Parameter you are trying to set.
Perhaps there is a TyPo.

Oh, and I believe the green text may be nuked as redundant.
 
Following your suggestions, some progress has been made:

Code: 
Dim cmd As ADODB.Command
Dim rst As ADODB.Recordset
Dim cnn As ADODB.Connection
Dim strConnect As String
strConnect = "Driver=MySQL ODBC 5.1 Driver;SERVER=localhost;UID=root;PWD=1234;DATABASE=sakila;PORT=3306"
Set cnn = New ADODB.Connection
cnn.Open strConnect
Set cmd = New ADODB.Command
With cmd
   .ActiveConnection = cnn
   .CommandText = "procjp3"
   .CommandType = adCmdStoredProc
   .Parameters.Refresh
[COLOR=red]   .Parameters("strParam").Value = "JOE"[/COLOR]
   Set rst = .Execute("")
End With
Dim blnDum As Boolean
With rst
    'Was no record found?
    If .BOF Or .EOF Then
      'Clear the class attributes
      'Me.Clear
      blnDum = False
    Else
      'Fetch the values found
[COLOR=lime]      MsgBox Nz(rst!first_name, 0)[/COLOR]
      'Set a good return code
      blnDum = True
    End If
    'Close the database table
    .Close
End With
Set rst = Nothing
Set cnn = Nothing
Set cmd = Nothing

I changed the contents of the red line from
Parameters("@first_name").Value 'the name of the field
to
Parameters("strParam").Value 'the name of the parameter with no @

I receive now the value of field first_name which is "JOE"

What if there are say 5 records where first_name = "JOE", can I display these 5 records on a tabular form?
 
JohnPapa said:
I changed the contents of the red line from
Parameters("@first_name").Value 'the name of the field
to
Parameters("strParam").Value 'the name of the parameter with no @
Click to expand...

Then it must be defined that way (name of Parameter) in the Stored Procedure.
JohnPapa said:
I receive now the value of field first_name which is "JOE"
Click to expand...

Woo hoo! Excellent! :D

JohnPapa said:
What if there are say 5 records where first_name = "JOE", can I display these 5 records on a tabular form?
Click to expand...

Then I would use the DAO method of executing the SP and download records into an FE temp table, which then you could display in a Multiple Items form.

There is no way to bind an Access Multiple Items Form to an adoRS object.

The reason I use adoCMD/adoRS objects with forms is to populate specific record Edit forms. I use my DAO scheme when feeding Multiple Items forms.

Oh, and you do not even need to send the empty quotes in this LOC:

Code: 
Set rst = .Execute("")
Empty () are acceptable.
 
Michael,

I was going through the DAO code for copying be table data to a temp fe table.

I was able to do the same by creating two recordsets, pointing to the fe and be tables and do the transfer. Am I missing something?

I was unable though to do something which appears simple. Pass as parameter the first letter of field first_name for example "A" and have all names that begin with "A" appear. the unsuccessful MySQL code is as follows:

Code: 
DELIMITER $$
USE `sakila`$$
DROP PROCEDURE IF EXISTS `procJP3`$$
CREATE DEFINER=`root`@`localhost` PROCEDURE `procJP3`(IN strParam VARCHAR(45))
BEGIN
        SELECT * FROM `actor` WHERE (`actor`.`first_name` LIKE '" &  strParam & "'%'"  );
    END$$
DELIMITER ;

where the call from Access would be something like

Code: 
CALL procjp3("A");

If I use CALL procjp3("ALEC"); where firstname = 'ALEC' the correct data is returned.
 
Last edited:
You must log in or register to reply here.

Similar threads

Gasman
32bit and 64bit?
Replies
4
Views
373
isladogs
isladogs
J
Search for possible declarations
Replies
9
Views
499
sonic8
S
A
Solved Database gets error message after transferring from Access 2010 to Runtime 2013
Replies
7
Views
436
Gasman
Gasman
B
Caller ID via TAPI (tapi32.dll) from phone phone PBX
Replies
1
Views
398
Uncle Gizmo
Uncle Gizmo
P
Why my custom VB.Net class file is giving an "Expected :Identifier" Error? What's wrong?
Replies
3
Views
282
prabha_friend
P

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom