Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 292
  • Last Modified:

how do i create database links in access through Visual Basic

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,

Neopost
0
Neopost
Asked:
Neopost
1 Solution
 
manchandaCommented:
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.ParamSetup
  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
LocalErr:
   '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
    'tdfLinked.RefreshLink
    dbsTemp.TableDefs.Refresh
    dbsTemp.Close
    Set dbsTemp = Nothing
    Set tdfLinked = Nothing
    'MsgBox "Table added"
    '''''RefreshDatabaseWindow
    'If tblEnum("YG4MTSum") Then
    'MsgBox "Table Exsist"
    'End If
LocalErr:
    '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
    objAppParam.ParamSetup
    '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
    'RefreshDatabaseWindow
   
LocalErr:
    '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
    objAppParam.ParamSetup
    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
    Next
   
End Function
0
 
CleanupPingCommented:
Neopost:
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 
EXPERTS:
Post your closing recommendations!  No comment means you don't care.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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