Re-Attach SQL tables via VBA to Access 2007 (1 Viewer)

tokoloshi

Registered User.
Local time
Today, 14:24
Joined
Jul 30, 2008
Messages
63
Anyone got any ideas on how to re-attach SQL Server linked tables please?
I need to re-create a connection with a different user after clicking a LOGIN button.
I have been running around in circles now trying to figure it out and am at the point where I need to ask for help.
I have tried the following:
---
Public Function SQLServerLinkedTableRefresh(CurrentUser, UserPwd)
Dim cat As ADOX.Catalog
Dim tbl As ADOX.Table
Set cat = New ADOX.Catalog
Set tbl = New ADOX.Table
Dim sConnString As String
' Set SQL Server connection string used in linked table.
sConnString = "ODBC;" & _
"Driver={SQL Server};" & _
"Server={DBSrv01};" & _
"Database=DebtMan;" & _
"Uid=" & CurrentUser & ";" & _
"Pwd=& UserPwd & ;"
' Open the catalog.
cat.ActiveConnection = CurrentProject.Connection
For Each tbl In cat.Tables
If tbl.Type = "PASS-THROUGH" And tbl.Name = "tbl_Person" Then
tbl.Properties("Jet OLEDB:Link Datasource") = sConnString ' Error here: "Cannot find field 'SID'"
End If
Next
End Function

I get an error: "Cannot find field 'SID'" at the point displayed. I seem to be chasing my tail a bit here.
Sean
 

Banana

split with a cherry atop.
Local time
Today, 05:24
Joined
Sep 1, 2005
Messages
6,318
Afraid I know nothing about MS SQL and Access 2007 (I use 2003).

But could you explain a bit why you want to do this?

Also, is DSN-less connection something you would consider? Google for Doug Steele DSN less connection; he has a excellent code sample to help accomplish this which also takes care of refresh as well. I use it myself (with lot of modifications due to the fact I'm using MySQL and implementing the security).
 

tokoloshi

Registered User.
Local time
Today, 14:24
Joined
Jul 30, 2008
Messages
63
But could you explain a bit why you want to do this?

Well, in SQL Server i can set up an OnUpdate, OnInsert or OnDelete trigger on a set of tables, pull out the current user and write the username, table affected and table changed into an audit table.

The only thing is, I need to ensure that the user is logged in using SQL Authentication and not Windows Authentication for a number of reasons, so I need the actual connection to all of the tables to be refreshed using the current user details instead of a DSN link.

I didn't want to have to take the route of DNS-less connections because i like the speed of development right now as I click on a linked table, click create a form, go into design mode, make a couple of changes and pick a check mark on a page to say that that form is also done.

All I want to do is relink all of the tables to SQL Server with the current user's credentials and leave SQL Server to handle the auditing.

Sean
 

Banana

split with a cherry atop.
Local time
Today, 05:24
Joined
Sep 1, 2005
Messages
6,318
Do you realize that DSN-less connection still can be used for linked tables as well? There is absolutely no difference between whether the linked table uses a DSN or DSN-less connection; it's identical within Access environment.
 

tokoloshi

Registered User.
Local time
Today, 14:24
Joined
Jul 30, 2008
Messages
63
Do you realize that DSN-less connection still can be used for linked tables as well? There is absolutely no difference between whether the linked table uses a DSN or DSN-less connection; it's identical within Access environment.

Banana you have just opened a whole world for me:D

I had never put the time aside to actually look into DSN-Less connections, because I felt that, if I need to go to that much trouble I might as well get the job done in VB, ASP or something else.

And there i thought I knew Access!

You have helped me out immensely - now to start working out how I should use this and when.

Does this mean that immediately after I have used the Access tools to link a table in I should run this routine? How do you use it with MySQL?

Should I develop a form that allows me to pick the tables i want to work with everytime?

Sean
 

Banana

split with a cherry atop.
Local time
Today, 05:24
Joined
Sep 1, 2005
Messages
6,318
Like I said, take a look at Doug Steele's excellent code sample on his website.

In my case, I have a unbound form as a startup for login with two unbound textbox for username and password. Once credential is entered and the button pushed, the password is copied into a variable and deleted from the textbox, and variable then gets salted & hashed then sent to the routine (what was left of Doug's poor sample after I took a machete to it... sorry, Doug!) which validates the credentials with the MySQL server then proceeds to re-create the tables with the connection string set for DSN-less connection, san the username/password (so there's nothing to mine from; Access is smart in reusing the open connection so you don't have to supply it everytime you access an object from backend).

Once I've connected, I process as normal in developing.

Yes, I do use DSN whenever I need to add a new table or object that wasn't previously in the Access. This also can be done in code with OpenDatabase (DAO) or Connection (ADO), but I'm just too lazy. Doug's routine will then pick it up next time it runs and convert it to DSN-less connection.

Did that help?
 

tokoloshi

Registered User.
Local time
Today, 14:24
Joined
Jul 30, 2008
Messages
63
Thanks a million.

I have been trying the code, with the various addendums. Getting "random" errors about tables that are not found.

Busy debugging & fault finding.

Will give a detailed breakdown when it all eventually works.

Thanks again.
 

Banana

split with a cherry atop.
Local time
Today, 05:24
Joined
Sep 1, 2005
Messages
6,318
Interesting. Doug's code is supposed to work out of the box (san the obvious modification to connection string for your own database & credentials).

I wonder if it's a 2007 thing (I don't know if his code was tested for 2007). Though his code is for SQL Server, if there are any differences, it wouldn't hurt to check with Carl Prothman for connection for your particular version and provider; google Carl Protham connection string for his website that lists all different possible connection strings for various backends and various providers.

HTH.
 

tokoloshi

Registered User.
Local time
Today, 14:24
Joined
Jul 30, 2008
Messages
63
Excellent! it is working like a dream.

Some things I needed to do initially are:

  1. Changed the routine to be a boolean function so that I can trap successful operation
  2. Pulled the Username & Password from the form
  3. Made sure that the users were created in SSMS and that they had the permissions to the databases. (I was unhappy about granting them all membership to db_owner group, but I will tighten this up later)
Other than that, everything is working fine.

What I will do is post up here the code for the trigger that I will write that will audit the tables as well, so that this acts as a complete repository for the solution.

The next thing to tackle is the security issues obviously and I still need to figure out how to use MD5 encryption from VBA/Access to SQL. If you have any thoughts on that I would be very grateful.

Thanks again.
 

Banana

split with a cherry atop.
Local time
Today, 05:24
Joined
Sep 1, 2005
Messages
6,318
Funnily enough, I did same thing myself (making it a function instead of a sub, etc)

I actually use SHA-256 *and* MD5; there's source code for VB6 (and will work in VBA) out there.

IMHO, this is the best resource for learning everything you need to know about cryptography if you have to implement your own security. There's a wealth of information that also are applicable to VB6 (VBA) to be digested.

Don't forget the salt to make the passwords harder to crack against a dictionary attack or rainbow tables. Ideally, you want to make sure that brute-force approach is the only approach that will crack the model the fastest (relative to other approach that is). The thing is that it's easy to fool yourself into thinking it has been secured but in fact there was a hole where they can just circumvent the model entirely. Indeed, if it requires social engineering to successfully hack the system, you've done all you can do and only need to whack the users on their head to not give out passwords to stranger or install Wow-o-Repair-the-Registry-And-Win-A-Ipod program.

As for backend, I suspect in order to have a linked table, one has to have a minimum of SELECT privilege to attach the the table, even though I ordinarily define security by columns for several of tables, not by the table itself. In such event, I just grant SELECT privilege to the columns, then use Access's User Level Security to deny permissions to tables and queries, and distribute this in MDE so it's quite hard to get the table definition (which I really don't care if they did because all they would find is columns designated for audit trails and they still don't have the privilege to edit it anyway). ULS can be implemented so that the default user 'Admin' has permissions to forms object, but no permission to the tables; this way you can distribute the application without a special .mdw and just use the default System.mdw and they don't have to log in for Access's security context; just for the backend's.
 

Users who are viewing this thread

Top Bottom