link to table on CD

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?
LVL 2
englishmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
 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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
threeps99Commented:
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
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
threeps,

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

Jim.
0
threeps99Commented:
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
englishmanAuthor Commented:
OK jdett,
It works fine; but how do I make the linked table look here?
0
englishmanAuthor Commented:
Will it work in both Access 97 and 2000?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
 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
englishmanAuthor Commented:
how do I get to the tabledef?
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
englishmanAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
englishmanAuthor Commented:
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
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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
englishmanAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.