?
Solved

link to table on CD

Posted on 1999-11-08
15
Medium Priority
?
249 Views
Last Modified: 2011-09-20
I need to link to a table on a CD-ROM drive but the letter of the drive could be C:, D:, etc.
Is there any naming convention that will help me overcome this problem?
0
Comment
Question by:englishman
  • 7
  • 6
  • 2
15 Comments
 
LVL 59
ID: 2191290
 None that I can think of.  It should be possible to use the Win API to get the drive letter of the CD-ROM, but what if a machine has more then one?

  Without some more detail, it's hard to say if this will work or not, but I'd just check the link and if it's not valid, prompt the user for the location of the file. You can find code to do this in the solutions database that came with Access.  This would also cover cases when a user adds a new HD and the CD-ROM drive letter might change.

Jim.
0
 
LVL 59
ID: 2191312
Low and behold, look what I stumbled over<g>.  

Jim.

Procedure

   ' **********************************************************************
   '
   ' FUNCTION:
   '    GetFirstCdRomDriveLetter()
   '
   ' PURPOSE:
   '    Finds the first CD-ROM device and then returns its drive letter.
   '
   ' ARGUMENTS:
   '    None
   '
   ' RETURNS:
   '    A string that represents the first CD-ROM drive letter. If the
   '    function fails for any reason, it returns vbNullString.
   '
   ' **********************************************************************
   Declare Function GetDriveType Lib "kernel32" Alias _
      "GetDriveTypeA" (ByVal nDrive As String) As Long

   Declare Function GetLogicalDriveStrings Lib "kernel32" Alias _
      "GetLogicalDriveStringsA" (ByVal nBufferLength As Long, _
      ByVal lpBuffer As String) As Long

   Public Const DRIVE_CDROM As Long = 5

   Function GetFirstCdRomDriveLetter() As String

      ' Declare variables.
      Dim lDriveType As Long
      Dim strDrive As String
      Dim lStart As Long: lStart = 1

      ' Create a string to hold the logical drives.
      Dim strDrives As String
      strDrives = Space(150)

      ' Get the logial drives on the system.
      ' If the function fails it returns zero.
      Dim lRetVal As Long
      lRetVal = GetLogicalDriveStrings(150, strDrives)

      ' Check to see if GetLogicalDriveStrings() worked.
      If lRetVal = 0 Then

         ' Get GetLogicalDriveStrings() failed.
         GetFirstCdRomDriveLetter = vbNullString
         Exit Function
      End If

      ' Get the string that represents the first drive.
      strDrive = Mid(strDrives, lStart, 3)

      Do

         ' Test the first drive.
         lDriveType = GetDriveType(strDrive)

         ' Check if the drive type is a CD-ROM.
         If lDriveType = DRIVE_CDROM Then

            ' Found the first CD-ROM drive on the system.
            GetFirstCdRomDriveLetter = strDrive
            Exit Function
         End If

         ' Increment lStart to next drive in the string.
         lStart = lStart + 4

         ' Get the string that represents the first drive.
         strDrive = Mid(strDrives, lStart, 3)

      Loop While (Mid(strDrives, lStart, 1) <> vbNullChar)

   End Function


'Following is an example of calling the GetFirstCdRomDriveLetter() function from a macro (Sub procedure):

   Sub Main

      Dim strDriveLetter as String

      ' Call the GetFirstCdRomDriveLetter() and store the
      ' return value in strDriveLetter.
      strDriveLetter = GetFirstCdRomDriveLetter()

      ' Display the drive letter in a message box.
      MsgBox strDriveLetter

   End Sub


0
 
LVL 4

Expert Comment

by:threeps99
ID: 2191317
JDettman ,

You get that from archives in this forum? I was just about to go looking for it. You may want to credit the expert who did it if so and the Q number.

threeps
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
LVL 59
ID: 2191354
threeps,

  No.  It came from the "NeatCode.MDB" published from Microsoft.  I didn't even know that EE had library capabilities.

Jim.
0
 
LVL 4

Expert Comment

by:threeps99
ID: 2191372
Ok,

I found the EE question too...

its

http://www.experts-exchange.com/jsp/qShow.jsp?ta=msaccess&qid=10205803 

Costs you 20 points, but cheaper than this :) Jim and Trygve give good answers to this almost exact problem.

threeps
0
 
LVL 2

Author Comment

by:englishman
ID: 2191551
OK jdett,
It works fine; but how do I make the linked table look here?
0
 
LVL 2

Author Comment

by:englishman
ID: 2191649
Will it work in both Access 97 and 2000?
0
 
LVL 59
ID: 2191768
 A linked table is nothing more that a TableDef with it's .Connect property set, so that's what your going to need to modify.  It will look something like this:

";DATABASE=C:\DBS\MYMDB.mdb",

 The ;DATABASE= tells JET that this is a native JET table.  The remainder of the string is the path to the MDB.  

Overall, it looks like this:

Sub ConnectSource()
 Dim dbs As Database, tdf As TableDef

 'Return reference to current database.
 Set dbs = CurrentDb
 ' Set TableDef object.
 Set tdf = dbs.TableDefs("myTable")
 ' Attach.
 tdf.Connect = ";DATABASE=C:\DBS\MYMDB.mdb"
 tdf.RefreshLink
 Set tdf = Nothing
 Set dbs = Nothing
End Sub

Jim.
0
 
LVL 2

Author Comment

by:englishman
ID: 2191964
how do I get to the tabledef?
0
 
LVL 59
ID: 2192454
Not quite sure what your asking here.  Did you look over the ConnectSource() code?

  It first gets a reference to the current database (dbs) and then sets a reference to the tabledef (tdf).  AFter that it modifies the .Connect property and refreshes the link.

Jim.
0
 
LVL 2

Author Comment

by:englishman
ID: 2193799
Still looking at what you sent above.
I have a db with, say, 6 tables. Instead of these tables I would change them to linked tables. But I need to use code to tell the forms etc to look at the cd-rom to link the tables to.
I'm not sure what to do with this last bit of code you sent. (code stupid here) Do I need to change all the code behind the forms etc.?
0
 
LVL 59

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 200 total points
ID: 2194040
No, the code behind the forms does not need to be changed.  Any code that you have will look in the current database for the table definition.  It's that definition that tells Access (via the .Connect and .SourceTableName properties) that the table is actually in another .MDB file.

  I'm assuming that this is something your going to distribute and you need to be able to allow the user to relink the tables are thier own.

  To do that, you need to do a couple of things (I'm assuming that all 6 tables are in the same MDB) in a function (I'd call it from the Autoexec macro).

1. Test the link on one of the tables.  If it's OK, do nothing.

2. If the link fails, get the CD-ROM drive letter using the function I posted.

3. Loop through the TableDefs collection and reset the .Connect property of each table with the new path.

4. Test the links again.  Are they now valid?  If yes quit.

5. If not, ask the user where they are located (maybe two CD-ROMS or the CD-ROM is not loaded).

6. Loop through the TableDefs collection and reset the .Connect property of each table with the new path.

  This is very close to what the RefreshLink() function does in the solutions.MDB sample database that comes with Access.  The only difference there is that it pops up an open file dialog to let the user locate the file rather then getting the CD-ROM drive letter like you want (it would skip steps 2-4 in the procedure above).

  I'm posting my own ReattachTables() below, which is similar to the version found in the solutions.MDB.  This function is called when I've determined that the links are broken.  The function accepts the name of an MDB, asks the user for the location (GetMDBName()), and then loops through the tabledefs collection and resets the .connect property.

  In your case, rather then call GetMDBName(), which pops up the open dialog, you'd get the CD-ROM letter and build a new string and use that to reattach the tables.

Hope that clears things up a bit!
JimD

Function ReattachTables(strDataMDB As String) As Integer
   
    Const NONEXISTENT_TABLE = 3011
    Const DATAMDB_NOT_FOUND = 3024
    Const ACCESS_DENIED = 3051
    Const READ_ONLY_DATABASE = 3027
   
    ' For file dialog
    Dim strFileName As String
    Dim strSearchPath As String
    Dim strTemp As String
    Dim varRet As Variant
    Dim strAccessDir As String
   
    ' For setting attachments.
    Dim curDB As DATABASE
    Dim curTableDef As TableDef
    Dim intNumberOfTables As Integer
    Dim intTableCount As Integer
    Dim strTable As String
    Dim strSourceTableName As String
    Dim i As Integer
   
    ' Get Location of MDB file
    strFileName = GetMDBName(strDataMDB)
    strFileName = Trim(strFileName)
    If strFileName = "" Then
        MsgBox "You can't run the application until you locate the " & strDataMDB & " database", 16, "Can't run the " & AppName()
        GoTo ReattachTables_Failed
    End If
   
    ' Loop through all tables, reattaching those with nonzero-length
    ' connect strings.
    Set curDB = DBEngine.Workspaces(0).Databases(0)
    intNumberOfTables = curDB.TableDefs.Count - 1
    varRet = SysCmd(SYSCMD_INITMETER, "Attaching tables", intNumberOfTables)
    intTableCount = 1
    i = 0

Do_Next_Table:
    Set curTableDef = curDB.TableDefs(i)
    If InStr(curTableDef.Connect, strDataMDB) > 0 Then
      strTable = curTableDef.Name
      strSourceTableName = curTableDef.SourceTableName
      Err = 0
      curDB.TableDefs.Delete strTable
      Set curTableDef = curDB.CreateTableDef(strTable)
      curTableDef.SourceTableName = strSourceTableName
      curTableDef.Connect = ";DATABASE=" & strFileName
      curDB.TableDefs.Append curTableDef
      If Err <> 0 Then
          If Err = NONEXISTENT_TABLE Then
            MsgBox "File '" & strFileName & "' does not contain required table '" & curTableDef.SourceTableName & "'", 16, "Can't run the " & AppName()
          ElseIf Err = DATAMDB_NOT_FOUND Then
            MsgBox "You can't run the application until you locate the " & strDataMDB & " database", 16, "Can't run the " & AppName()
          ElseIf Err = ACCESS_DENIED Then
            MsgBox "Couldn't open " & strFileName & " because it is read-only or it is located on a read-only share.", 16, "Can't run the " & AppName()
          ElseIf Err = READ_ONLY_DATABASE Then
            MsgBox "Can't reattach tables because" & strDataMDB & "is read-only or is located on a read-only share.", 16, "Can't run the " & AppName()
          Else
            MsgBox Error, 16, "Can't run the " & AppName()
          End If
          varRet = SysCmd(SYSCMD_REMOVEMETER)
          GoTo ReattachTables_Failed
      End If
      intNumberOfTables = intNumberOfTables - 1
      intTableCount = intTableCount + 1
      varRet = SysCmd(SYSCMD_UPDATEMETER, intTableCount)
    Else
      i = i + 1
    End If

    If i <= intNumberOfTables Then GoTo Do_Next_Table

ReattachTables_Exit:
    ReattachTables = True
    varRet = SysCmd(SYSCMD_REMOVEMETER)
    Set curDB = Nothing
   
    Exit Function

ReattachTables_Failed:
    ReattachTables = False
    Set curDB = Nothing
    Call ApplicationExit

End Function


0
 
LVL 2

Author Comment

by:englishman
ID: 2196662
Brilliant!
Grab the points.
I didn't use the last code you posted.
For reference, this is how I did it.
Please comment if it is done inefficiently.

Created a linked table to solutions9.mdb on the Office Developer CD.
Put the code below in a module and called the function linktables from an autoexec macro.

----------------------------------------------------------
Declare Function GetDriveType Lib "kernel32" Alias _
      "GetDriveTypeA" (ByVal nDrive As String) As Long

   Declare Function GetLogicalDriveStrings Lib "kernel32" Alias _
      "GetLogicalDriveStringsA" (ByVal nBufferLength As Long, _
      ByVal lpBuffer As String) As Long

   Public Const DRIVE_CDROM As Long = 5
----------------------------------------------------------------
   Function GetFirstCdRomDriveLetter() As String

      ' Declare variables.
      Dim lDriveType As Long
      Dim strDrive As String
      Dim lStart As Long: lStart = 1

      ' Create a string to hold the logical drives.
      Dim strDrives As String
      strDrives = Space(150)

      ' Get the logial drives on the system.
      ' If the function fails it returns zero.
      Dim lRetVal As Long
      lRetVal = GetLogicalDriveStrings(150, strDrives)

      ' Check to see if GetLogicalDriveStrings() worked.
      If lRetVal = 0 Then

         ' Get GetLogicalDriveStrings() failed.
         GetFirstCdRomDriveLetter = vbNullString
         Exit Function
      End If

      ' Get the string that represents the first drive.
      strDrive = Mid(strDrives, lStart, 3)

      Do

         ' Test the first drive.
         lDriveType = GetDriveType(strDrive)

         ' Check if the drive type is a CD-ROM.
         If lDriveType = DRIVE_CDROM Then

            ' Found the first CD-ROM drive on the system.
            GetFirstCdRomDriveLetter = strDrive
            Exit Function
         End If

         ' Increment lStart to next drive in the string.
         lStart = lStart + 4

         ' Get the string that represents the first drive.
         strDrive = Mid(strDrives, lStart, 3)

      Loop While (Mid(strDrives, lStart, 1) <> vbNullChar)

   End Function
----------------------------------------------
   
Sub ConnectSource()
Dim strDriveLetter As String

      ' Call the GetFirstCdRomDriveLetter() and store the
      ' return value in strDriveLetter.
      strDriveLetter = GetFirstCdRomDriveLetter()
      'MsgBox strDriveLetter
 'Dim dbs As Database, tdf As TableDef
Dim dbs, tdf
 'Return reference to current database.
 Set dbs = CurrentDb
 ' Set TableDef object.
 Set tdf = dbs.TableDefs("examples")
 ' Attach.
 tdf.Connect = ";DATABASE=" & strDriveLetter & "ODETools\v9\Samples\OPG\Samples\CH05\Solutions9.mdb"
 tdf.RefreshLink
 Set tdf = Nothing
 Set dbs = Nothing
End Sub
------------------------------------------
Function linktables()
ConnectSource
End Function
0
 
LVL 59
ID: 2197215
Glad to hear you've got it!  Please accept the last comment as an answer (which I'm told you can now do on EE).

Take care,
Jim.
0
 
LVL 2

Author Comment

by:englishman
ID: 2197620
Brilliant!
Grab the points.
I didn't use the last code you posted.
For reference, this is how I did it.
Please comment if it is done inefficiently.

Created a linked table to solutions9.mdb on the Office Developer CD.
Put the code below in a module and called the function linktables from an autoexec macro.

----------------------------------------------------------
Declare Function GetDriveType Lib "kernel32" Alias _
      "GetDriveTypeA" (ByVal nDrive As String) As Long

   Declare Function GetLogicalDriveStrings Lib "kernel32" Alias _
      "GetLogicalDriveStringsA" (ByVal nBufferLength As Long, _
      ByVal lpBuffer As String) As Long

   Public Const DRIVE_CDROM As Long = 5
----------------------------------------------------------------
   Function GetFirstCdRomDriveLetter() As String

      ' Declare variables.
      Dim lDriveType As Long
      Dim strDrive As String
      Dim lStart As Long: lStart = 1

      ' Create a string to hold the logical drives.
      Dim strDrives As String
      strDrives = Space(150)

      ' Get the logial drives on the system.
      ' If the function fails it returns zero.
      Dim lRetVal As Long
      lRetVal = GetLogicalDriveStrings(150, strDrives)

      ' Check to see if GetLogicalDriveStrings() worked.
      If lRetVal = 0 Then

         ' Get GetLogicalDriveStrings() failed.
         GetFirstCdRomDriveLetter = vbNullString
         Exit Function
      End If

      ' Get the string that represents the first drive.
      strDrive = Mid(strDrives, lStart, 3)

      Do

         ' Test the first drive.
         lDriveType = GetDriveType(strDrive)

         ' Check if the drive type is a CD-ROM.
         If lDriveType = DRIVE_CDROM Then

            ' Found the first CD-ROM drive on the system.
            GetFirstCdRomDriveLetter = strDrive
            Exit Function
         End If

         ' Increment lStart to next drive in the string.
         lStart = lStart + 4

         ' Get the string that represents the first drive.
         strDrive = Mid(strDrives, lStart, 3)

      Loop While (Mid(strDrives, lStart, 1) <> vbNullChar)

   End Function
----------------------------------------------
   
Sub ConnectSource()
Dim strDriveLetter As String

      ' Call the GetFirstCdRomDriveLetter() and store the
      ' return value in strDriveLetter.
      strDriveLetter = GetFirstCdRomDriveLetter()
      'MsgBox strDriveLetter
 'Dim dbs As Database, tdf As TableDef
Dim dbs, tdf
 'Return reference to current database.
 Set dbs = CurrentDb
 ' Set TableDef object.
 Set tdf = dbs.TableDefs("examples")
 ' Attach.
 tdf.Connect = ";DATABASE=" & strDriveLetter & "ODETools\v9\Samples\OPG\Samples\CH05\Solutions9.mdb"
 tdf.RefreshLink
 Set tdf = Nothing
 Set dbs = Nothing
End Sub
------------------------------------------
Function linktables()
ConnectSource
End Function
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer lets you open cabinet (cab) files like any other folder. In VBA you can easily handle normal files and folders, but opening and indeed creating cabinet files takes a lot more - and that's you'll find here.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

599 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