I have a database that needs to obt

[DPUser]

I have a database that needs to obtain its data from another database through an ODBC. I am using transferdatabase to obtain the linked tables. I want to be able to have the user enter the user id and password. How can I substitute a string variable in the place where user id goes?
My code is as follows:


Dim datsource1 As String
Dim cnn1 As New ADODB.Connection
Dim rst1 As ADODB.Recordset
DataSource.SetFocus
datasource1 = DataSource.Text

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=datasource & ";UID=icstpass;PWD=ictspass;DATABASE=stamprod_sql", acTable, "exch_tools_trade", "dbo_exch_tools_trade"

Thank you

 

[Savil]

Try an input box

DoCmd.TransferDatabase acLink, "ODBC Database", "ODBC;DSN=datasource & ";UID='" & inputbox("Please enter userID&quot & "';PWD='" & inputBox("Please enter password&quot & "';DATABASE=stamprod_sql", acTable, "exch_tools_trade", "dbo_exch_tools_trade"

I've never tried or tested it but I see no reason why it should not work

Savil