Import tables via odbc

[bhc92]

Hell,

I have tried some of the methods posted here but I have been unsuccessful. I have a small ms access db that I used to run SLA and other reports of an internal support system that is on a Mysql db. I am able to link the table without problem, but I want to write a routine in order to import 15 tables automatically.

I have the server details but when I try to connect I am getting a connection failure message. Could somebody help with the code and how I would set this up the the routinge to import 14 or so tables.

Many thanks,

Rob.

 

[CyberLynx]

To start with, I have programaticaly Linked Access tables to from Backend to Frontend but have never had the need to Link to a MySQL table. I think the concept is still pretty much the same with the exception of the Connection String required and the need for the MyODBC driver. Here is a shot at it and perhaps it will get you moving in the right direction.

To link tables from MySQL you need to get the MyODBC driver and install it. It's Free here.

The best way to do this is I think, is by placing the names of the tables you want to link from MySQL into a MS-Access table and then Reference that table every time you open the Access Database in order to carry out the necessary links.

Create a function in your Splash Form or whatever Form is started first when MS-Access is started. For the Sake of argument, let's call it LinkMySQLTables():

Private Function LinkMySQLTables() As Boolean
   'DAO 3.x Required
   'Declare Variables...
   Dim ConnectStrg As String, rst As Recordset, _
       db As Database, Strg As String
   
   'The Connection String required to connect to MySQL.
   'You will need to fill in the proper information within
   'this string.
   ConnectStrg = "DRIVER={MySQL ODBC 3.51 Driver};" & _
                 "Server=myServerName;" & _
                 "Port=3306;" & _
                 "Option=16384;" & _
                 "Stmt=;" & _
                 "Database=mydatabaseName;" & _
                 "Uid=myUsername;" & _
                 "Pwd=myPassword"

   'Trap any Errors...
   On Error GoTo Error_LinkMySQLTables
   
   'Open a recordset from the table the conatains
   'all the table names we want to Link from the
   'MySQL Database.
   Set db = CurrentDb
   Set rst = db.OpenRecordset("NickSeenStatus", dbOpenSnapshot)
   With rst
      'Fill the Recordset...
      .MoveLast
      .MoveFirst
      'Enumerate through the Records...
      Do Until rst.EOF
         'Place the Table Name into the Strg string variable.
         ' FieldName (below) would be the Field name in your Access
         ' Table which holds the name of the MySQL Tables to Link.
         Strg = !FieldName
         'Make sure we are not dealing will an empty string..
         If Len(Strg) > 0 Then
            'Link the MySQL Table to this Database.
            DoCmd.TransferDatabase acLink, "ODBC Database", ConnectStrg, _
                  acTable, Strg, Strg
         End If
         'move to the next record...
         .MoveNext
      Loop
   End With
   'We're done...
   
Exit_LinkMySQLTables:
   'Clear Variables and close the db connection.
   Set rst = Nothing
   If Not db Is Nothing Then db.Close
   Set db = Nothing
   Exit Function
   
Error_LinkMySQLTables:
   'If there was an error then display the Error Msg.
   MsgBox "Link Tables Error:" & vbCr & vbCr & _
          Err.Number & "  -  " & Err.Description, _
          vbExclamation, "Table Link Error"
   Err.Clear
   Resume Exit_LinkMySQLTables
End Function

Then in the Form's OnOpen event enter the code:

Call LinkMySQLTables

Anyways...give that a whirl.

I forgot to mention... If you need other connect strings then you can try here.
.

 

[Chromium]

Does not work cause of an error "3170 Can't find installable ISAM."

(NB I use Access 2007 and 2003)

Any suggestions?

 

[Banana]

More info needed.

What line does it trips up on?
Any modification to the code?
Was that an Access error or ODBC error?

 

[CyberLynx]

Install the ISAM driver. Should be free from Microsoft.

.

 

[Chromium]

Install the ISAM driver. Should be free from Microsoft.

.

Solved with ISAM driver. I had to reinstall MSOffice.

 

[access fan]

CyberLynx,

I have been trying to use your code but I keep on getting error 2507. It seems to be in the connection string?? I am using access 2003 and W7. Any suggestions?

thanks,