Hi so I have a database that links to one main backend, however, one of the tables will become incredibly large(contains mass amounts of financial data) and I would like to link this table to a separate database.
On our main menu the user selects the particular fund that they will be working on, on the after update event of this control I am trying to refresh the link for that table, to a different backend database that simply contains that table and the specific data for that fund.
I have read countless forums that all seem to be following the same process, however I cannot get it to work for me and can not figure out why. I am very new to VBA and cant seem to figure it out. Any help or suggestions would be greatly appreciated.
In addition to the code posted below I have also tried variations with refreshing the link instead which also didnt work.
Here is the code I am currently trying to use:
Function ReLinkTable(strTbl As String) As Boolean
Dim dbsTmp As DAO.Database
Dim tdfTmp As DAO.TableDef
Dim strPrefix As String
Dim strFundID As String
Dim strPath As String
Dim strNewConnect As String
On Error GoTo Proc_Err
Set dbsTmp = CurrentDb()
dbsTmp.TableDefs.Delete (strTable)
Set tdfTmp = dbsTmp.CreateTableDefs(strTable)
strID = str(Forms!frmMainMenu!Id)
'real path removed for sesitivity
strPath = "My Path" & Trim(strTable & strID & ".accdb")
With tdfTmp
tdfTmp.Connect = ";DATABASE=" & ";" & strPath
tdfTmp.SourceTableName = strTable
End With
dbsTmp.TableDefs.Append tdfTmp
ReLinkTable = True
Proc_Exit:
dbsTmp.Close
Exit Function
Proc_Err:
ReLinkTable = False
Resume Proc_Exit
End Function
On our main menu the user selects the particular fund that they will be working on, on the after update event of this control I am trying to refresh the link for that table, to a different backend database that simply contains that table and the specific data for that fund.
I have read countless forums that all seem to be following the same process, however I cannot get it to work for me and can not figure out why. I am very new to VBA and cant seem to figure it out. Any help or suggestions would be greatly appreciated.
In addition to the code posted below I have also tried variations with refreshing the link instead which also didnt work.
Here is the code I am currently trying to use:
Function ReLinkTable(strTbl As String) As Boolean
Dim dbsTmp As DAO.Database
Dim tdfTmp As DAO.TableDef
Dim strPrefix As String
Dim strFundID As String
Dim strPath As String
Dim strNewConnect As String
On Error GoTo Proc_Err
Set dbsTmp = CurrentDb()
dbsTmp.TableDefs.Delete (strTable)
Set tdfTmp = dbsTmp.CreateTableDefs(strTable)
strID = str(Forms!frmMainMenu!Id)
'real path removed for sesitivity
strPath = "My Path" & Trim(strTable & strID & ".accdb")
With tdfTmp
tdfTmp.Connect = ";DATABASE=" & ";" & strPath
tdfTmp.SourceTableName = strTable
End With
dbsTmp.TableDefs.Append tdfTmp
ReLinkTable = True
Proc_Exit:
dbsTmp.Close
Exit Function
Proc_Err:
ReLinkTable = False
Resume Proc_Exit
End Function