pr*************@gmail.com (Prakash Wadhwani) wrote in message news:<ca**************************@posting.google. com>...
I have an Access Database called "TEMP"
On Opening the "TEMP" Database, I need to use VBA (programmatically):
a) to connect to another database called "PMF"
b) the PMF database requires a password on opening called "allow"
c) to link 5 Tables in the PMF Database ... Table1,2,3,4,5
How do I accomplish the above programmatically using VBA ?
Thx & Best Rgds,
Prakash.
Straight outta Compton!!, umm I mean the help file...
The only modification you need to make is to accept a username and
password combination to the database in question, and open a workspace
with that user/pwd combination.
Open a Workspace (from the AccessXP help):
CreateWorkspace Method Example
This example uses the CreateWorkspace method to create both a
Microsoft Jet workspace and an ODBCDirect workspace. It then lists the
properties of both types of workspace.
Sub CreateWorkspaceX()
Dim wrkODBC As Workspace
Dim wrkJet As Workspace
Dim wrkLoop As Workspace
Dim prpLoop As Property
' Create an ODBCDirect workspace. Until you create
' Microsoft Jet workspace, the Microsoft Jet database
' engine will not be loaded into memory.
Set wrkODBC = CreateWorkspace("ODBCWorkspace", "admin", _
"", dbUseODBC)
Workspaces.Append wrkODBC
DefaultType = dbUseJet
' Create an unnamed Workspace object of the type
' specified by the DefaultType property of DBEngine
' (dbUseJet).
Set wrkJet = CreateWorkspace("", "admin", "")
' Enumerate Workspaces collection.
Debug.Print "Workspace objects in Workspaces collection:"
For Each wrkLoop In Workspaces
Debug.Print " " & wrkLoop.Name
Next wrkLoop
With wrkODBC
' Enumerate Properties collection of ODBCDirect
' workspace.
Debug.Print "Properties of " & .Name
On Error Resume Next
For Each prpLoop In .Properties
Debug.Print " " & prpLoop.Name & " = " & prpLoop
Next prpLoop
On Error GoTo 0
End With
With wrkJet
' Enumerate Properties collection of Microsoft Jet
' workspace.
Debug.Print _
"Properties of unnamed Microsoft Jet workspace"
On Error Resume Next
For Each prpLoop In .Properties
Debug.Print " " & prpLoop.Name & " = " & prpLoop
Next prpLoop
On Error GoTo 0
End With
wrkODBC.Close
wrkJet.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
Help is your FRIEND. Read. Learn. Be more productive.
