Solved

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

Posted on 2004-04-01
7
247 Views
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.
0
Comment
Question by:Mitleid242
  • 3
  • 2
7 Comments
 
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
   dbsNorthwind.Close

End Sub
0
 

Author Comment

by:Mitleid242
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.
0
 
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", _
            "Employees"
      Case 2
         ConnectOutput dbsTemp, _
            "FoxProTable", _
            "FoxPro 3.0;DATABASE=C:\FoxPro30\Samples", _
            "Q1Sales"
      Case 3
         ConnectOutput dbsTemp, _
            "dBASETable", _
            "dBase IV;DATABASE=C:\dBASE\Samples", _
            "Accounts"
      Case 4
         ConnectOutput dbsTemp, _
            "ParadoxTable", _
            "Paradox 3.X;DATABASE=C:\Paradox\Samples", _
            "Accounts"
      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", _
            "THIRDQTR"
      Case 7
         ConnectOutput dbsTemp, _
            "CSVTable", _
            "Text;DATABASE=C:\Samples", _
            "Sample.txt"
      Case 8
         ConnectOutput dbsTemp, _
            "HTMLTable", _
            "HTML Import;DATABASE=http://" & _
               "www.server1.com/samples/page1.html", _
            "Q1SalesData"
      Case 9
         ConnectOutput dbsTemp, _
            "ExchangeTable", _
            "Exchange 4.0;MAPILEVEL=" & _
               "Mailbox - Michelle Wortman (Exchange)" & _
               "|People\Important;", _
            "Jerry Wheeler"
   End Select

   dbsTemp.Close

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
         .MoveNext
      Loop
      If Not .EOF Then Debug.Print , "[additional records]"
      .Close
   End With

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

End Sub

0
 

Author Comment

by:Mitleid242
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.
0
 
LVL 50

Accepted Solution

by:
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.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now