how do i  create database links in access through Visual Basic

Posted on 2003-02-27
Medium Priority
Last Modified: 2009-12-16
I have an access front end database which has links to other access databases. But i wont to decide the paths for the linked tables in Visual Basic.

Any one got any bright ideas?

Thanks in advance,

Question by:Neopost
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Accepted Solution

manchanda earned 200 total points
ID: 8034941
som thing like this:
Public Function S1SImportDB(objAppDB As Object, strDBName As String, strTableName As String, strTableType As String)
  Dim strConnect As String
  Dim objAppParam As New clsAppParam
  objAppParam.strDataFile = strDBName
  On Error GoTo LocalErr
  'YG4MTSUMBuild 'First build YG4MTSUM in S1S
    ' Open a Microsoft Jet database to which you will link
    ' a table.
    If strTableType = "dbase" Then
    strConnect = "dBase III;DATABASE=" & objAppParam.strDataFilePath
    End If
    If strTableType = "access" Then
    strConnect = ";DATABASE=" & objAppParam.strDataFilePath & objAppParam.strDataFile & objAppParam.strpwd
    End If
    If strTableType = "Excel" Then
    strConnect = "Excel 5.0;HDR=YES;IMEX=2;DATABASE=" & objAppParam.strDataFilePath & objAppParam.strDataFile & objAppParam.strpwd
    End If
    ''''Dim strAppDatabase As String
    '''''strAppDatabase = objAppParam.strAppFilePath & objAppParam.strAppFile
    ''''Set dbsTemp = OpenDatabase(strAppDatabase)
    'If tblEnum(strTableName) = False Then
    ''''ConnectOutput dbsTemp, strTableName, strConnect, strTableName
    ConnectOutput objAppDB, strTableName, strConnect, strTableName
    'End If
    Set dbsTemp = Nothing
   'Resume Next
    'MsgBox Err.LastDllError
    'MsgBox Err.Number
    'MsgBox Err.Description
End Function

Sub ConnectOutput(dbsTemp As Database, strTable As String, strConnect As String, _
strSourceTable As String)
    On Error GoTo LocalErr
    Dim tdfLinked As TableDef
    ' 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 dbsTemp = Nothing
    Set tdfLinked = Nothing
    'MsgBox "Table added"
    'If tblEnum("YG4MTSum") Then
    'MsgBox "Table Exsist"
    'End If
    'Resume Next
    'MsgBox Err.LastDllError
    'MsgBox Err.Number
    If Right(CurrentDb.NAME, 3) = "MDB" Then
    MsgBox Err.Description
    End If
    'Err.Raise Err.Number
    End Sub

Public Function S1SExportTable(strTableName As String)
  ' Delete the linked table because this is a demonstration.
    On Error GoTo LocalErr
    Dim strAppDatabase As String
    Dim objAppParam As New clsAppParam
    'Try for Log off audit
     Dim objLog As New clsLog
     objLog.WriteLog "Logoff from this form"
     strAppDatabase = objAppParam.strAppFilePath & objAppParam.strAppFile
    Set dbsTemp = OpenDatabase(strAppDatabase)
    If tblEnum(strTableName) Then
    dbsTemp.TableDefs.Delete strTableName
    'MsgBox "DEleted AL4DD122"
    End If
    Set dbsTemp = Nothing
    'Resume Next
    'MsgBox Err.LastDllError
    'MsgBox Err.Number
    'MsgBox Err.Description
    'Err.Raise Err.Number
End Function

Function tblEnum(strTableName) As Boolean
    Dim intNumberTable As Integer
    Dim strAppDatabase As String
    Dim objAppParam As New clsAppParam
    strAppDatabase = objAppParam.strAppFilePath & objAppParam.strAppFile
    Dim i As Integer
    Dim db As Database
    Set db = OpenDatabase(strAppDatabase)
    intNumberTable = db.TableDefs.Count
    tblEnum = False
    For i = 0 To intNumberTable - 1
        'MsgBox CurrentDb.TableDefs(i).NAME
        If db.TableDefs(i).NAME = strTableName Then
            tblEnum = True
            i = intNumberTable
        End If
End Function

Expert Comment

ID: 9447465
This old question needs to be finalized -- accept an answer, split points, or get a refund.  For information on your options, please click here-> http:/help/closing.jsp#1 
Post your closing recommendations!  No comment means you don't care.

Featured Post

Get real performance insights from real users

Key features:
- Total Pages Views and Load times
- Top Pages Viewed and Load Times
- Real Time Site Page Build Performance
- Users’ Browser and Platform Performance
- Geographic User Breakdown
- And more

Question has a verified solution.

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

A short article about problems I had with the new location API and permissions in Marshmallow
Make the most of your online learning experience.
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
Suggested Courses

801 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