By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,574 Members | 3,021 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,574 IT Pros & Developers. It's quick & easy.

connect to MS SQL with different user/password in VBA

matthardwick
P: 19
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.
Jul 27 '08 #1
Share this Question
Share on Google+
6 Replies


nico5038
Expert 2.5K+
P: 3,072
Guess this link provides the needed information:
http://support.microsoft.com/kb/245587

Nic;o)
Jul 27 '08 #2

matthardwick
P: 19
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.
Jul 27 '08 #3

nico5038
Expert 2.5K+
P: 3,072
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)
Jul 27 '08 #4

matthardwick
P: 19
I tried this:
Expand|Select|Wrap|Line Numbers
  1.    For Each X In CurrentDb.TableDefs
  2.     If Not X.Properties(4) = "" Then
  3.         strXProp = X.Properties(4)
  4.         strXProp = Replace(strXProp, "normal", "readonly")
  5.         strXProp = Replace(strXProp, "1234", "abcd")
  6.         X.Connect = strXProp
  7.         X.RefreshLink
  8.     End If
  9.    Next X
  10.  
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)
Jul 27 '08 #5

nico5038
Expert 2.5K+
P: 3,072
For relinking tables you can use this code:
Expand|Select|Wrap|Line Numbers
  1. Function fncRelink()
  2. 'function to relink tables to the "_be" database
  3. 'It's assumed that the "_be" database is in the same folder as the frontend !
  4.  
  5. Dim td As DAO.TableDef
  6.  
  7. For Each td In CurrentDb.TableDefs
  8.   If Len(td.Connect) > 0 Then
  9.      td.Connect = ";DATABASE=" & Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\")) & Mid(td.Connect, InStrRev(td.Connect, "\") + 1)
  10.      td.RefreshLink
  11.   End If
  12. Next
  13.  
  14. MsgBox "Ready, tables relinked"
  15.  
  16. End Function
  17.  
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)
Jul 27 '08 #6

matthardwick
P: 19
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.
Jul 27 '08 #7

Post your reply

Sign in to post your reply or Sign up for a free account.