bconner
04-28-2011, 08:35 AM
All,
I am trying to change the ODBC Connection string based on the value of a combo box. For Example if a user selects Grp 4 from the combo box I want to reset the connection strings of the linked tables to
If Forms![FrmConnection].[cmbGroup].Value = "GRP 4" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup4;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup4;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
etc....
The Linked tables are in SQL Server. I linked the tables and then changed the table name in access. I have tried using the SQL Server table name CurrentDb.TableDefs("dbo_atb_tbl").Connect = as well as the Access table name CurrentDb.TableDefs("atbtbl").Connect = and neither works. I get error 3265 "Item Not Found in this collection"
I have even tried tables("dbo_atb_tbl").Connect and tables("atbtbl").Connect and neither works......
Any help is greatly appreciated.....
Below is the entire Function I am trying to use
Function Refresh()
'CurrentDb.QueryTimeout = 60
Screen.MousePointer = 10
On Error GoTo Error_Msg
' Refresh CurrentDb.TableDefs
If Forms![FrmConnection].[cmbGroup].Value = "GRP 5" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup5;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup5;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
If Forms![FrmConnection].[cmbGroup].Value = "GRP 4" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup4;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup4;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
If Forms![FrmConnection].[cmbGroup].Value = "GRP 7" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup7;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup7;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
If Forms![FrmConnection].[cmbGroup].Value = "GRP 11" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup11;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup11;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
If Forms![FrmConnection].[cmbGroup].Value = "GRP 12" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup12;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup12;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
If Forms![FrmConnection].[cmbGroup].Value = "GRP 8" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup8;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup8;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
'Refresh Dictionaries
CurrentDb.TableDefs("Dictionary 120").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("Dictionary 120").RefreshLink
CurrentDb.TableDefs("FSC Flow cast").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("FSC Flow cast").RefreshLink
CurrentDb.TableDefs("Location List Flow Cast").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("Location List Flow Cast").RefreshLink
CurrentDb.TableDefs("PAYCODE Flow Cast").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("PAYCODE Flow Cast").RefreshLink
CurrentDb.TableDefs("Provider MASTER").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("Provider MASTER").RefreshLink
CurrentDb.TableDefs("REJECTIONS MASTER").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("REJECTIONS MASTER").RefreshLink
RefreshCurrentDb.TableDefsLinks = True
Exit Function
Error_Msg:
MsgBox Err.Number & Err.Description
RefreshCurrentDb.TableDefsLinks = False
Screen.MousePointer = 0
End Function
I am trying to change the ODBC Connection string based on the value of a combo box. For Example if a user selects Grp 4 from the combo box I want to reset the connection strings of the linked tables to
If Forms![FrmConnection].[cmbGroup].Value = "GRP 4" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup4;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup4;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
etc....
The Linked tables are in SQL Server. I linked the tables and then changed the table name in access. I have tried using the SQL Server table name CurrentDb.TableDefs("dbo_atb_tbl").Connect = as well as the Access table name CurrentDb.TableDefs("atbtbl").Connect = and neither works. I get error 3265 "Item Not Found in this collection"
I have even tried tables("dbo_atb_tbl").Connect and tables("atbtbl").Connect and neither works......
Any help is greatly appreciated.....
Below is the entire Function I am trying to use
Function Refresh()
'CurrentDb.QueryTimeout = 60
Screen.MousePointer = 10
On Error GoTo Error_Msg
' Refresh CurrentDb.TableDefs
If Forms![FrmConnection].[cmbGroup].Value = "GRP 5" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup5;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup5;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
If Forms![FrmConnection].[cmbGroup].Value = "GRP 4" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup4;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup4;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
If Forms![FrmConnection].[cmbGroup].Value = "GRP 7" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup7;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup7;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
If Forms![FrmConnection].[cmbGroup].Value = "GRP 11" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup11;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup11;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
If Forms![FrmConnection].[cmbGroup].Value = "GRP 12" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup12;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup12;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
If Forms![FrmConnection].[cmbGroup].Value = "GRP 8" Then
CurrentDb.TableDefs("dbo_atb_tbl").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup8;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_atb_tbl").RefreshLink
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= DeepDiveGroup8;uid=admin;pwd=admin;"
CurrentDb.TableDefs("dbo_Credits_Payments_Contractual_BadDebt").RefreshLink
End If
'Refresh Dictionaries
CurrentDb.TableDefs("Dictionary 120").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("Dictionary 120").RefreshLink
CurrentDb.TableDefs("FSC Flow cast").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("FSC Flow cast").RefreshLink
CurrentDb.TableDefs("Location List Flow Cast").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("Location List Flow Cast").RefreshLink
CurrentDb.TableDefs("PAYCODE Flow Cast").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("PAYCODE Flow Cast").RefreshLink
CurrentDb.TableDefs("Provider MASTER").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("Provider MASTER").RefreshLink
CurrentDb.TableDefs("REJECTIONS MASTER").Connect = "ODBC;DRIVER=SQL Server;SERVER=addwddtls7b1\billinganalytic2;Trusted_Connection=No;Database= Dictionaries;uid=admin;pwd=admin;"
CurrentDb.TableDefs("REJECTIONS MASTER").RefreshLink
RefreshCurrentDb.TableDefsLinks = True
Exit Function
Error_Msg:
MsgBox Err.Number & Err.Description
RefreshCurrentDb.TableDefsLinks = False
Screen.MousePointer = 0
End Function