Improve company productivity with a Business Account.Sign Up

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 271
  • Last Modified:

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

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.
  • 3
  • 2
1 Solution
Steve BinkCommented:
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
Mitleid242Author Commented:
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.
Steve BinkCommented:
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

Mitleid242Author Commented:
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.
Steve BinkCommented:
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.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now