Changing location of linked table through code? 2

[Guest_imported]

I am linking to DBase tables and need to be able to change the location of the source files without deleting the table (if the table is deleted all the relationships go out the window) this has to be code driven from user selection of the new path. What is the variable (if any) which contains the source location of linked tables and how do you go about editing it?

Thanks
GPC

 

[jes]

Hi There,

If you set view system objects to true through Tools->Options, you will get a table displayed called msysobjects.

Within this is a field called Database which can be changed at will(or so I believe).

If you need anything more, just shout,

Byeee

 

[Aivars]

Hi!

There are example from Help:

Connect and SourceTableName Properties Example

This example uses the Connect and SourceTableName properties to link various external tables to a Microsoft Jet database. The ConnectOutput procedure is required for this procedure to run.

Sub ConnectX()

Dim dbsTemp As Database
Dim strMenu As String
Dim strInput As String

' Open a Microsoft Jet database to which you will link
' a table.
Set dbsTemp = OpenDatabase("DB1.mdb&quot

' Build menu text.
strMenu = "Enter number for data source:" & vbCr
strMenu = strMenu & _
" 1. Microsoft Jet database" & vbCr
strMenu = strMenu & _

" 2. dBASE table" & vbCr
strMenu = strMenu & _
" 3. Paradox table" & vbCr
strMenu = strMenu & _
" M. (see choices 5-9)"

' Get user's choice.
strInput = InputBox(strMenu)

If UCase(strInput) = "M" Then

' Build menu text.
strMenu = "Enter number for data source:" & vbCr
strMenu = strMenu & _
" 4. Microsoft Excel spreadsheet" & vbCr
strMenu = strMenu & _
" 5. Lotus spreadsheet" & vbCr
strMenu = strMenu & _
" 6. Comma-delimited text (CSV)" & vbCr
strMenu = strMenu & _
" 7. HTML table" & vbCr
strMenu = strMenu & _
" 8. Microsoft Exchange folder"

' Get user's choice.
strInput = InputBox(strMenu)

End If

' Call the ConnectOutput procedure. The third argument
' will be used as the Connect string, and the fourth
' argument will be used as the SourceTableName.
Select Case Val(strInput)
Case 1
ConnectOutput dbsTemp, _
"JetTable", _
";DATABASE=C:\My Documents\Northwind.mdb", _
"Employees"

Case 2
ConnectOutput dbsTemp, _
"dBASETable", _
"dBase IV;DATABASE=C:\dBASE\Samples", _
"Accounts"
Case 3
ConnectOutput dbsTemp, _
"ParadoxTable", _
"Paradox 3.X;DATABASE=C:\Paradox\Samples", _
"Accounts"
Case 4
ConnectOutput dbsTemp, _
"ExcelTable", _
"Excel 5.0;" & _
"DATABASE=C:\Excel\Samples\Q1Sales.xls", _
"January Sales"
Case 5
ConnectOutput dbsTemp, _
"LotusTable", _
"Lotus WK3;" & _
"DATABASE=C:\Lotus\Samples\Sales.xls", _
"THIRDQTR"
Case 6
ConnectOutput dbsTemp, _
"CSVTable", _
"Text;DATABASE=C:\Samples", _
"Sample.txt"
Case 7
ConnectOutput dbsTemp, _
"HTMLTable", _
"HTML Import;DATABASE=

http://"

& _
"

http://www.server1.com/samples/page1.html",

_
"Q1SalesData"
Case 8
ConnectOutput dbsTemp, _
"ExchangeTable", _
"Exchange 4.0;MAPILEVEL=" & _
"Mailbox - Michelle Wortman (Exchange)" & _
"|People\Important;", _
"Jerry Wheeler"
End Select

dbsTemp.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 , &quot;[additional records]&quot;
.Close
End With

' Delete the linked table because this is a demonstration.
dbsTemp.TableDefs.Delete strTable

End Sub

Aivars

 

[Guest_imported]

Thanks all, have used similar:

Set tbls = CurrentDb.TableDefs
Set tbl = tbls(&quot;...&quot

tbl.Connect = &quot;dBase IV;HDR=NO;IMEX=2;DATABASE=C:\...&quot;
tbl.RefreshLink
tbls.Refresh

GC

 

[magister]

When I run the above code in VBA I get an error message on the database type. Please explain.

thanks