connect to MS SQL with different user/password in VBA

[matthardwick]

Hi There,

I currently have all my tables in MS SQL and they are connecting in the standard way (used the external data section to link to the tables through ODBC in Office 2007 and the MS SQL 2005 Native Client).

I was wondering if there was a way in VBA before my database does anything, to specify a different username/password to connect to the SQL server with? I have 2 SQL Authentication usernames (one is read-only) and I have the two DSN files with the slightly different usernames and passwords in them.

Is there a way in VBA to specify different username and password OR to choose a different DSN file to use?

Thanks.

Matt.

[nico5038]

Guess this link provides the needed information:
http://support.microsoft.com/kb/245587

Nic;o)

[matthardwick]

Thanks but that didn't work. It's really old code, and just wouldn't work in Access 2007 - threw no errors, but I was still getting prompted if there was no username and password, or it just wouldn't open the tables.

[nico5038]

Perhaps better to switch to an Access Data Project:
http://msdn.microsoft.com/library/de...essProject.asp
The data access section will show the needed Access 2007 connection code.

Nic;o)

[matthardwick]

I tried this:

   For Each X In CurrentDb.TableDefs
    If Not X.Properties(4) = "" Then
        strXProp = X.Properties(4)
        strXProp = Replace(strXProp, "normal", "readonly")
        strXProp = Replace(strXProp, "1234", "abcd")
        X.Connect = strXProp
        X.RefreshLink
    End If
   Next X
 

but it doesn't work straight away. Instead it saves that to the tables properties (which is OK because I can just do the reverse when I need to) - but it means that I have to restart access, which isn't ideal.

I did look at an ADP - but coding in that would be quite different to a MDB (I already found a few functions that didn't work just in my basic testing)

[nico5038]

For relinking tables you can use this code:

Function fncRelink()
'function to relink tables to the "_be" database
'It's assumed that the "_be" database is in the same folder as the frontend !
 
Dim td As DAO.TableDef
 
For Each td In CurrentDb.TableDefs
  If Len(td.Connect) > 0 Then
     td.Connect = ";DATABASE=" & Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) & Mid(td.Connect, InStrRev(td.Connect, "\") + 1)
     td.RefreshLink
  End If
Next
 
MsgBox "Ready, tables relinked"
 
End Function
 

You'l have to edit the td.Connect to reflect the needed userid and password. Just open a linked table in design mode and check the properties, as it will show the link used.

Nic;o)

[matthardwick]

That's basically the same code, and it's what I started with (which didn't work) before I turned it into that. Property 4 is the same as connect.
All I did was just replace the old username and password with the new one.

And it's not another database that I need to connect to, it's the same SQL server - just need to change the username and password.

Thanks anyway.

Looks like we will just have to go with ADP.