Linking tables from button on switchboard

Because I have a number of projects for which data needs to be kept separate
I have created a split database with the intention that I could copy the
created back end to create a blank set of tables and when I had a new project
that I could take the blank tables and create a copy with an appropriate
name, ie projectxtables and then set up a button on a switchboard to select
the new back end tables. This would lead to a switchboard with a series of
buttons calling various project tables. All back end tables have the same
structure.

I came across the following when trying to find help but it gives me a -
Run-time error '3265' Item not found in collection - when I try to run it. I
obviously changed the directory and file name to the back end I created (back
end created by copying back end file and giving new name as described above).

"Josh wrote:
I would like to change the absolute path of a linked table in code behind a
button.
I have a linked table named "TestTable" and would like to refresh the link
to C:\test\testdb.mdb


The path to the backend table is stored in the linked
table's Connect property. See Help for details.

If the backend is a Jet database (MDB, MDE, etc) file the
code would look like:

Dim db As DAO.database
Dim tdf As DAO.TableDef
Dim strPath As String
strPath = "C:\test\testdb.mdb"

Set db = CurrentDb()
Set tdf = db.TableDefs!linkedtable
tdf.Connect = ";DATABASE=" & strPath
tdf.RefreshLink
Set tdf = Nothing
Set db = Nothing

--
Marsh
MVP [MS Access]"

Surely what I am trying to do is not unusual but as a beginner in access I
have not yet the knowledge to do it.

Let me correct your code

Sub VerifyLinkPath()
Dim db As Database
Dim LoadTables As TableDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("_LinkTablesConfigure", dbOpenDynaset)

rst.MoveFirst
Do
With rst
Dim strDBName: strDBName = !LinkTable
Dim strOriginalPath: strOriginalPath = fGetLinkPath(strDBName)
Dim strNewPath: strNewPath = !PathLinkTable
If (strOriginalPath strNewPath) Then
'MsgBox ("que oso wey")
Set LoadTables = db.TableDefs(!LinkTable)
With LoadTables
Dim strConnect: strConnect = .Connect
strConnect = Replace(strConnect, strOriginalPath,
strNewPath)
.Connect = strConnect
.RefreshLink
MsgBox ("path " & strDBName)
End With
Set LoadTables = Nothing
End If
.MoveNext
End With
Loop Until rst.EOF

Set rst = Nothing
Set db = Nothing

End Sub

Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String

Set dbs = CurrentDb()
On Error Resume Next
stPath = dbs.TableDefs(strTable).Connect
If stPath = "" Then
fGetLinkPath = vbNullString
'can change this to currentdb.name
Else
fGetLinkPath = Right(stPath, Len(stPath) _
- (InStr(1, stPath, "DATABASE=") + 8))
End If
Set dbs = Nothing
End Function


"chesterman" wrote:

Because I have a number of projects for which data needs to be kept separate
I have created a split database with the intention that I could copy the
created back end to create a blank set of tables and when I had a new project
that I could take the blank tables and create a copy with an appropriate
name, ie projectxtables and then set up a button on a switchboard to select
the new back end tables. This would lead to a switchboard with a series of
buttons calling various project tables. All back end tables have the same
structure.

I came across the following when trying to find help but it gives me a -
Run-time error '3265' Item not found in collection - when I try to run it. I
obviously changed the directory and file name to the back end I created (back
end created by copying back end file and giving new name as described above).

"Josh wrote:
I would like to change the absolute path of a linked table in code behind a
button.
I have a linked table named "TestTable" and would like to refresh the link
to C:\test\testdb.mdb


The path to the backend table is stored in the linked
table's Connect property. See Help for details.

If the backend is a Jet database (MDB, MDE, etc) file the
code would look like:

Dim db As DAO.database
Dim tdf As DAO.TableDef
Dim strPath As String
strPath = "C:\test\testdb.mdb"

Set db = CurrentDb()
Set tdf = db.TableDefs!linkedtable
tdf.Connect = ";DATABASE=" & strPath
tdf.RefreshLink
Set tdf = Nothing
Set db = Nothing

--
Marsh
MVP [MS Access]"

Surely what I am trying to do is not unusual but as a beginner in access I
have not yet the knowledge to do it.

Tried code but when trying to run to test get Compile Error: ByRef arguement
type mismatch - on clicking OK Sub VerifyLinkPath() is highlighted yellow and
StrDBName is highlighted blue. Also I wanted code to automatically link to
back end table indicated by button when the button is selected without
further user input, ie c:\db\backend1 and I cannot see how this code knows
what table to link to.

I am keen to learn and would appreciate any detailed help and guidance on
what I am doing wrong.

"ArbolNet" wrote:

Let me correct your code

Sub VerifyLinkPath()
Dim db As Database
Dim LoadTables As TableDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("_LinkTablesConfigure", dbOpenDynaset)

rst.MoveFirst
Do
With rst
Dim strDBName: strDBName = !LinkTable
Dim strOriginalPath: strOriginalPath = fGetLinkPath(strDBName)
Dim strNewPath: strNewPath = !PathLinkTable
If (strOriginalPath strNewPath) Then
'MsgBox ("que oso wey")
Set LoadTables = db.TableDefs(!LinkTable)
With LoadTables
Dim strConnect: strConnect = .Connect
strConnect = Replace(strConnect, strOriginalPath,
strNewPath)
.Connect = strConnect
.RefreshLink
MsgBox ("path " & strDBName)
End With
Set LoadTables = Nothing
End If
.MoveNext
End With
Loop Until rst.EOF

Set rst = Nothing
Set db = Nothing

End Sub

Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String

Set dbs = CurrentDb()
On Error Resume Next
stPath = dbs.TableDefs(strTable).Connect
If stPath = "" Then
fGetLinkPath = vbNullString
'can change this to currentdb.name
Else
fGetLinkPath = Right(stPath, Len(stPath) _
- (InStr(1, stPath, "DATABASE=") + 8))
End If
Set dbs = Nothing
End Function


"chesterman" wrote:

Because I have a number of projects for which data needs to be kept separate
I have created a split database with the intention that I could copy the
created back end to create a blank set of tables and when I had a new project
that I could take the blank tables and create a copy with an appropriate
name, ie projectxtables and then set up a button on a switchboard to select
the new back end tables. This would lead to a switchboard with a series of
buttons calling various project tables. All back end tables have the same
structure.

I came across the following when trying to find help but it gives me a -
Run-time error '3265' Item not found in collection - when I try to run it. I
obviously changed the directory and file name to the back end I created (back
end created by copying back end file and giving new name as described above).

"Josh wrote:
I would like to change the absolute path of a linked table in code behind a
button.
I have a linked table named "TestTable" and would like to refresh the link
to C:\test\testdb.mdb


The path to the backend table is stored in the linked
table's Connect property. See Help for details.

If the backend is a Jet database (MDB, MDE, etc) file the
code would look like:

Dim db As DAO.database
Dim tdf As DAO.TableDef
Dim strPath As String
strPath = "C:\test\testdb.mdb"

Set db = CurrentDb()
Set tdf = db.TableDefs!linkedtable
tdf.Connect = ";DATABASE=" & strPath
tdf.RefreshLink
Set tdf = Nothing
Set db = Nothing

--
Marsh
MVP [MS Access]"

Surely what I am trying to do is not unusual but as a beginner in access I
have not yet the knowledge to do it.

No my friend.....

You need more help, let my try it

"_LinkTablesConfigure" is a table inside the MyTable.mdb, this table have 2
columns (LinkTable, PathLinkTable), think about it!, your broke link name is:
c:/temp/dbTest.mdb (if access file) -- PathLinkTable
And your db is:
db_test -- LinkTable

or (if a txt file)
c:/temp/ -- PathLinkTable
txtFile -- LinkTable

You need insert this values inside "_LinkTablesConfigure" and run this script

Good look



"chesterman" wrote:

Tried code but when trying to run to test get Compile Error: ByRef arguement
type mismatch - on clicking OK Sub VerifyLinkPath() is highlighted yellow and
StrDBName is highlighted blue. Also I wanted code to automatically link to
back end table indicated by button when the button is selected without
further user input, ie c:\db\backend1 and I cannot see how this code knows
what table to link to.

I am keen to learn and would appreciate any detailed help and guidance on
what I am doing wrong.

"ArbolNet" wrote:

Let me correct your code

Sub VerifyLinkPath()
Dim db As Database
Dim LoadTables As TableDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("_LinkTablesConfigure", dbOpenDynaset)

rst.MoveFirst
Do
With rst
Dim strDBName: strDBName = !LinkTable
Dim strOriginalPath: strOriginalPath = fGetLinkPath(strDBName)
Dim strNewPath: strNewPath = !PathLinkTable
If (strOriginalPath strNewPath) Then
'MsgBox ("que oso wey")
Set LoadTables = db.TableDefs(!LinkTable)
With LoadTables
Dim strConnect: strConnect = .Connect
strConnect = Replace(strConnect, strOriginalPath,
strNewPath)
.Connect = strConnect
.RefreshLink
MsgBox ("path " & strDBName)
End With
Set LoadTables = Nothing
End If
.MoveNext
End With
Loop Until rst.EOF

Set rst = Nothing
Set db = Nothing

End Sub

Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String

Set dbs = CurrentDb()
On Error Resume Next
stPath = dbs.TableDefs(strTable).Connect
If stPath = "" Then
fGetLinkPath = vbNullString
'can change this to currentdb.name
Else
fGetLinkPath = Right(stPath, Len(stPath) _
- (InStr(1, stPath, "DATABASE=") + 8))
End If
Set dbs = Nothing
End Function


"chesterman" wrote:

Because I have a number of projects for which data needs to be kept separate
I have created a split database with the intention that I could copy the
created back end to create a blank set of tables and when I had a new project
that I could take the blank tables and create a copy with an appropriate
name, ie projectxtables and then set up a button on a switchboard to select
the new back end tables. This would lead to a switchboard with a series of
buttons calling various project tables. All back end tables have the same
structure.

I came across the following when trying to find help but it gives me a -
Run-time error '3265' Item not found in collection - when I try to run it. I
obviously changed the directory and file name to the back end I created (back
end created by copying back end file and giving new name as described above).

"Josh wrote:
I would like to change the absolute path of a linked table in code behind a
button.
I have a linked table named "TestTable" and would like to refresh the link
to C:\test\testdb.mdb


The path to the backend table is stored in the linked
table's Connect property. See Help for details.

If the backend is a Jet database (MDB, MDE, etc) file the
code would look like:

Dim db As DAO.database
Dim tdf As DAO.TableDef
Dim strPath As String
strPath = "C:\test\testdb.mdb"

Set db = CurrentDb()
Set tdf = db.TableDefs!linkedtable
tdf.Connect = ";DATABASE=" & strPath
tdf.RefreshLink
Set tdf = Nothing
Set db = Nothing

--
Marsh
MVP [MS Access]"

Surely what I am trying to do is not unusual but as a beginner in access I
have not yet the knowledge to do it.

Thanks for your help and patience I now understand.

"ArbolNet" wrote:

No my friend.....

You need more help, let my try it

"_LinkTablesConfigure" is a table inside the MyTable.mdb, this table have 2
columns (LinkTable, PathLinkTable), think about it!, your broke link name is:
c:/temp/dbTest.mdb (if access file) -- PathLinkTable
And your db is:
db_test -- LinkTable

or (if a txt file)
c:/temp/ -- PathLinkTable
txtFile -- LinkTable

You need insert this values inside "_LinkTablesConfigure" and run this script

Good look



"chesterman" wrote:

Tried code but when trying to run to test get Compile Error: ByRef arguement
type mismatch - on clicking OK Sub VerifyLinkPath() is highlighted yellow and
StrDBName is highlighted blue. Also I wanted code to automatically link to
back end table indicated by button when the button is selected without
further user input, ie c:\db\backend1 and I cannot see how this code knows
what table to link to.

I am keen to learn and would appreciate any detailed help and guidance on
what I am doing wrong.

"ArbolNet" wrote:

Let me correct your code

Sub VerifyLinkPath()
Dim db As Database
Dim LoadTables As TableDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("_LinkTablesConfigure", dbOpenDynaset)

rst.MoveFirst
Do
With rst
Dim strDBName: strDBName = !LinkTable
Dim strOriginalPath: strOriginalPath = fGetLinkPath(strDBName)
Dim strNewPath: strNewPath = !PathLinkTable
If (strOriginalPath strNewPath) Then
'MsgBox ("que oso wey")
Set LoadTables = db.TableDefs(!LinkTable)
With LoadTables
Dim strConnect: strConnect = .Connect
strConnect = Replace(strConnect, strOriginalPath,
strNewPath)
.Connect = strConnect
.RefreshLink
MsgBox ("path " & strDBName)
End With
Set LoadTables = Nothing
End If
.MoveNext
End With
Loop Until rst.EOF

Set rst = Nothing
Set db = Nothing

End Sub

Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String

Set dbs = CurrentDb()
On Error Resume Next
stPath = dbs.TableDefs(strTable).Connect
If stPath = "" Then
fGetLinkPath = vbNullString
'can change this to currentdb.name
Else
fGetLinkPath = Right(stPath, Len(stPath) _
- (InStr(1, stPath, "DATABASE=") + 8))
End If
Set dbs = Nothing
End Function


"chesterman" wrote:

Because I have a number of projects for which data needs to be kept separate
I have created a split database with the intention that I could copy the
created back end to create a blank set of tables and when I had a new project
that I could take the blank tables and create a copy with an appropriate
name, ie projectxtables and then set up a button on a switchboard to select
the new back end tables. This would lead to a switchboard with a series of
buttons calling various project tables. All back end tables have the same
structure.

I came across the following when trying to find help but it gives me a -
Run-time error '3265' Item not found in collection - when I try to run it. I
obviously changed the directory and file name to the back end I created (back
end created by copying back end file and giving new name as described above).

"Josh wrote:
I would like to change the absolute path of a linked table in code behind a
button.
I have a linked table named "TestTable" and would like to refresh the link
to C:\test\testdb.mdb


The path to the backend table is stored in the linked
table's Connect property. See Help for details.

If the backend is a Jet database (MDB, MDE, etc) file the
code would look like:

Dim db As DAO.database
Dim tdf As DAO.TableDef
Dim strPath As String
strPath = "C:\test\testdb.mdb"

Set db = CurrentDb()
Set tdf = db.TableDefs!linkedtable
tdf.Connect = ";DATABASE=" & strPath
tdf.RefreshLink
Set tdf = Nothing
Set db = Nothing

--
Marsh
MVP [MS Access]"

Surely what I am trying to do is not unusual but as a beginner in access I
have not yet the knowledge to do it.

Sorry for my english .... I know ... is not good.

on the other hand, I detected a bug in this code, you require to change this
sentence:
Dim strDBName: strDBName = !LinkTable
this maybe caused error msg.
pleace change for this
Dim strDBName As String
strDBName = !LinkTable
in orden to resolved

Good look my friend


"chesterman" wrote:

Thanks for your help and patience I now understand.

"ArbolNet" wrote:

No my friend.....

You need more help, let my try it

"_LinkTablesConfigure" is a table inside the MyTable.mdb, this table have 2
columns (LinkTable, PathLinkTable), think about it!, your broke link name is:
c:/temp/dbTest.mdb (if access file) -- PathLinkTable
And your db is:
db_test -- LinkTable

or (if a txt file)
c:/temp/ -- PathLinkTable
txtFile -- LinkTable

You need insert this values inside "_LinkTablesConfigure" and run this script

Good look



"chesterman" wrote:

Tried code but when trying to run to test get Compile Error: ByRef arguement
type mismatch - on clicking OK Sub VerifyLinkPath() is highlighted yellow and
StrDBName is highlighted blue. Also I wanted code to automatically link to
back end table indicated by button when the button is selected without
further user input, ie c:\db\backend1 and I cannot see how this code knows
what table to link to.

I am keen to learn and would appreciate any detailed help and guidance on
what I am doing wrong.

"ArbolNet" wrote:

Let me correct your code

Sub VerifyLinkPath()
Dim db As Database
Dim LoadTables As TableDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("_LinkTablesConfigure", dbOpenDynaset)

rst.MoveFirst
Do
With rst
Dim strDBName: strDBName = !LinkTable
Dim strOriginalPath: strOriginalPath = fGetLinkPath(strDBName)
Dim strNewPath: strNewPath = !PathLinkTable
If (strOriginalPath strNewPath) Then
'MsgBox ("que oso wey")
Set LoadTables = db.TableDefs(!LinkTable)
With LoadTables
Dim strConnect: strConnect = .Connect
strConnect = Replace(strConnect, strOriginalPath,
strNewPath)
.Connect = strConnect
.RefreshLink
MsgBox ("path " & strDBName)
End With
Set LoadTables = Nothing
End If
.MoveNext
End With
Loop Until rst.EOF

Set rst = Nothing
Set db = Nothing

End Sub

Function fGetLinkPath(strTable As String) As String
Dim dbs As Database, stPath As String

Set dbs = CurrentDb()
On Error Resume Next
stPath = dbs.TableDefs(strTable).Connect
If stPath = "" Then
fGetLinkPath = vbNullString
'can change this to currentdb.name
Else
fGetLinkPath = Right(stPath, Len(stPath) _
- (InStr(1, stPath, "DATABASE=") + 8))
End If
Set dbs = Nothing
End Function


"chesterman" wrote:

Because I have a number of projects for which data needs to be kept separate
I have created a split database with the intention that I could copy the
created back end to create a blank set of tables and when I had a new project
that I could take the blank tables and create a copy with an appropriate
name, ie projectxtables and then set up a button on a switchboard to select
the new back end tables. This would lead to a switchboard with a series of
buttons calling various project tables. All back end tables have the same
structure.

I came across the following when trying to find help but it gives me a -
Run-time error '3265' Item not found in collection - when I try to run it. I
obviously changed the directory and file name to the back end I created (back
end created by copying back end file and giving new name as described above).

"Josh wrote:
I would like to change the absolute path of a linked table in code behind a
button.
I have a linked table named "TestTable" and would like to refresh the link
to C:\test\testdb.mdb


The path to the backend table is stored in the linked
table's Connect property. See Help for details.

If the backend is a Jet database (MDB, MDE, etc) file the
code would look like:

Dim db As DAO.database
Dim tdf As DAO.TableDef
Dim strPath As String
strPath = "C:\test\testdb.mdb"

Set db = CurrentDb()
Set tdf = db.TableDefs!linkedtable
tdf.Connect = ";DATABASE=" & strPath
tdf.RefreshLink
Set tdf = Nothing
Set db = Nothing

--
Marsh
MVP [MS Access]"

Surely what I am trying to do is not unusual but as a beginner in access I
have not yet the knowledge to do it.