Building a MySQL linked table in Access 2K with VB code...

Posted on 2004-04-01
Last Modified: 2012-06-27
Hello. I've been working on an MS Access front-end that uses a MySQL server as a backend, and I've hit a snag regarding some security issues. Since I've established all of the MySQL table information as linked within the actual front-end application, that means technically any user can manually view whatever data is listed in those linked tables. So far, this is OK as none of that data is particularly sensitive. Now I've gotten into some aspects of the system that require a bit more security, and the solution I've come up with is the following...

Since I'm using Access, I don't see the point in using ADO to open a database connection everytime the user needs to view/edit data. This isn't a web application, and I don't have as much control over data entry with Access as I might with a web application, so my conclusion is that the best way to go about having the user access data is to restrict it to linked tables. That way, it's a bit more "robust" for Access to get to whatever information it needs to from those tables. So, to quit babbling, what I've decided to do is have a user authenticate themselves to be able to access the data within the more sensitive areas of the MySQL server (I've already built that...), and then I'd like to have Access, preferabbly via ADO code, build a linked table to the perticular databases. My plan is to leave that linked table on the database until the user exits out of the particular sections of the application that no longer require it. Since each user is accessing through their own .mdb file, the creation of a linked table on the local database on their machine seems like the best bet. While It's not 100 percent, it didn't sound like too shabby an idea to me. But then again, what do I know...?

Anyways, the snag I've hit is actually writing the code to do this. I'm not very familiar with ADO (or even DAO) for that matter, so I'm a bit lost on how to go about building a linked table and deleting it via the VB code in my Access application. As of now, there is only one table that needs to be linked up, but there could be more in the future, in case that has any influence on possible answers. Anyways, I'd greatly appreciate whatever creative ideas anyone has in helping me devise a solution to this. Thanks a lot.
Question by:Mitleid242
  • 3
  • 2
LVL 50

Expert Comment

by:Steve Bink
ID: 10735122
Using DAO, this can be done very easily.  Take a look at the TableDefs collection.  In particular, the example as shown in VBA help is shown below.  To make this a linked table, you would have to specify the .Connect and .SourceTableName properties for the new table, but you would not have to append all the fields (it would pull that information from the source).  The example for the linked table version is waaay long, but you can find it easily enough through the Object Browser.  Find the TableDef object, highlight the Connect property, and press F1.  When you click on "Example" in the resulting help pane, select "Connect and SourceTableName Properties (DAO)" from the list.

CreateField Method Example

This example uses the CreateField method to create three Fields for a new TableDef. It then displays the properties of those Field objects that are automatically set by the CreateField method. (Properties whose values are empty at the time of Field creation are not shown.)

Sub CreateFieldX()

   Dim dbsNorthwind As Database
   Dim tdfNew As TableDef
   Dim fldLoop As Field
   Dim prpLoop As Property

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")

   Set tdfNew = dbsNorthwind.CreateTableDef("NewTableDef")

   ' Create and append new Field objects for the new
   ' TableDef object.
   With tdfNew
      ' The CreateField method will set a default Size
      ' for a new Field object if one is not specified.
      .Fields.Append .CreateField("TextField", dbText)
      .Fields.Append .CreateField("IntegerField", dbInteger)
      .Fields.Append .CreateField("DateField", dbDate)
   End With

   dbsNorthwind.TableDefs.Append tdfNew

   Debug.Print "Properties of new Fields in " & tdfNew.Name

   ' Enumerate Fields collection to show the properties of
   ' the new Field objects.
   For Each fldLoop In tdfNew.Fields
      Debug.Print "  " & fldLoop.Name

      For Each prpLoop In fldLoop.Properties
         ' Properties that are invalid in the context of
         ' TableDefs will trigger an error if an attempt
         ' is made to read their values.
         On Error Resume Next
         Debug.Print "    " & prpLoop.Name & " - " & _
            IIf(prpLoop = "", "[empty]", prpLoop)
         On Error GoTo 0
      Next prpLoop

   Next fldLoop

   ' Delete new TableDef because this is a demonstration.
   dbsNorthwind.TableDefs.Delete tdfNew.Name

End Sub

Author Comment

ID: 10735218
Thanks for the input, routinet. I've actually already used some DAO for updating some tabledefs in my application, but I was only sucessfully if I used a local ODBC connection saved on the user's machine as a reference. From what I understand, DAO isn't as flexible when it comes to dynamically accessing remote data sources, which is exactly what I need at this point. I'd like the source to be built entirely in the VB code and with the password the user enters and have no reliance upon the data sources saved on the local users' machine, if that is at all possible.
LVL 50

Expert Comment

by:Steve Bink
ID: 10735416
It is possible, as shown in the example I didn't post.  :)  I'm posting it now...told ya it was long.  

Connect and SourceTableName Properties Example

This example uses the Connect and SourceTableName properties to link various external tables to a Microsoft Jet database. The ConnectOutput procedure is required for this procedure to run.   [My Comment: the required procedure is at the bottom]

Sub ConnectX()

   Dim dbsTemp As Database
   Dim strMenu As String
   Dim strInput As String

   ' Open a Microsoft Jet database to which you will link
   ' a table.
   Set dbsTemp = OpenDatabase("DB1.mdb")

   ' Build menu text.
   strMenu = "Enter number for data source:" & vbCr
   strMenu = strMenu & _
      " 1. Microsoft Jet database" & vbCr
   strMenu = strMenu & _
      " 2. Microsoft FoxPro 3.0 table" & vbCr
   strMenu = strMenu & _
      " 3. dBASE table" & vbCr
   strMenu = strMenu & _
      " 4. Paradox table" & vbCr
   strMenu = strMenu & _
      " M. (see choices 5-9)"

   ' Get user's choice.
   strInput = InputBox(strMenu)

   If UCase(strInput) = "M" Then

      ' Build menu text.
      strMenu = "Enter number for data source:" & vbCr
      strMenu = strMenu & _
         " 5. Microsoft Excel spreadsheet" & vbCr
      strMenu = strMenu & _
         " 6. Lotus spreadsheet" & vbCr
      strMenu = strMenu & _
         " 7. Comma-delimited text (CSV)" & vbCr
      strMenu = strMenu & _
         " 8. HTML table" & vbCr
      strMenu = strMenu & _
         " 9. Microsoft Exchange folder"

      ' Get user's choice.
      strInput = InputBox(strMenu)

   End If

   ' Call the ConnectOutput procedure. The third argument
   ' will be used as the Connect string, and the fourth
   ' argument will be used as the SourceTableName.
   Select Case Val(strInput)
      Case 1
         ConnectOutput dbsTemp, _
            "JetTable", _
            ";DATABASE=C:\My Documents\Northwind.mdb", _
      Case 2
         ConnectOutput dbsTemp, _
            "FoxProTable", _
            "FoxPro 3.0;DATABASE=C:\FoxPro30\Samples", _
      Case 3
         ConnectOutput dbsTemp, _
            "dBASETable", _
            "dBase IV;DATABASE=C:\dBASE\Samples", _
      Case 4
         ConnectOutput dbsTemp, _
            "ParadoxTable", _
            "Paradox 3.X;DATABASE=C:\Paradox\Samples", _
      Case 5
         ConnectOutput dbsTemp, _
            "ExcelTable", _
            "Excel 5.0;" & _
               "DATABASE=C:\Excel\Samples\Q1Sales.xls", _
            "January Sales"
      Case 6
         ConnectOutput dbsTemp, _
            "LotusTable", _
            "Lotus WK3;" & _
               "DATABASE=C:\Lotus\Samples\Sales.xls", _
      Case 7
         ConnectOutput dbsTemp, _
            "CSVTable", _
            "Text;DATABASE=C:\Samples", _
      Case 8
         ConnectOutput dbsTemp, _
            "HTMLTable", _
            "HTML Import;DATABASE=http://" & _
               "", _
      Case 9
         ConnectOutput dbsTemp, _
            "ExchangeTable", _
            "Exchange 4.0;MAPILEVEL=" & _
               "Mailbox - Michelle Wortman (Exchange)" & _
               "|People\Important;", _
            "Jerry Wheeler"
   End Select


End Sub

Sub ConnectOutput(dbsTemp As Database, _
   strTable As String, strConnect As String, _
   strSourceTable As String)

   Dim tdfLinked As TableDef
   Dim rstLinked As Recordset
   Dim intTemp As Integer

   ' Create a new TableDef, set its Connect and
   ' SourceTableName properties based on the passed
   ' arguments, and append it to the TableDefs collection.
   Set tdfLinked = dbsTemp.CreateTableDef(strTable)

   tdfLinked.Connect = strConnect
   tdfLinked.SourceTableName = strSourceTable
   dbsTemp.TableDefs.Append tdfLinked

   Set rstLinked = dbsTemp.OpenRecordset(strTable)

   Debug.Print "Data from linked table:"

   ' Display the first three records of the linked table.
   intTemp = 1
   With rstLinked
      Do While Not .EOF And intTemp <= 3
         Debug.Print , .Fields(0), .Fields(1)
         intTemp = intTemp + 1
      If Not .EOF Then Debug.Print , "[additional records]"
   End With

   ' Delete the linked table because this is a demonstration.
   dbsTemp.TableDefs.Delete strTable

End Sub


Author Comment

ID: 10735596
Thanks again, routinet. You've taught me some very useful info; try the help file, no matter how disjointed it may seem.

Anyways, I think I'm getting the concepts.... I have no idea why I'm having such a hard time grasping the ADO/DAO library; I've been struggling with it forever it seems... Heh. BTW, if anyone has a very NEWBIE-oriented but informative tutorial/guide for ADO they can direct me to, that'd be great. On the other hand, if someone can suggest a specific connect string to use to connect to a MySQL server (as all the attempts I've made with DAO have failed), I could probably have this problem solved soon enough.
LVL 50

Accepted Solution

Steve Bink earned 250 total points
ID: 10735929
Yeah, the help files do suck.  :)  I found it through the Object Browser...wouldn't have a clue of where to find it in the normal help.

I've been using 2 texts to help with all this connectivity stuff.  Well, one text with a backup that I've opened once.  The good one is:

"Beginning Access 2002 VBA" from Wrox Publishing, in their P2P (Programmer to Programmer) series.  They cover everything from basic concepts to in-depth coding in a very straight-forward, easy to understand format.  A CD is included with the text.  Overall a very good series...I use the same set for ASP.

The backup:
"Using Microsoft Office Access 2003, Special Edition" from Que Publishing.  Que generally puts out decent texts, but their relevency is pretty hit-or-miss when it comes to specific applications.

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Phishing attempts can come in all forms, shapes and sizes. No matter how familiar you think you are with them, always remember to take extra precaution when opening an email with attachments or links.
It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question