Linking table access / sql

Question

Linking table access / sql

Rich Reich

Valued Member

Points: 65
More actions
March 20, 2006 at 7:14 am

#189737

I have a large SQL database with 480 tables and had a programmer develop a Access application to enter data into 10 of those tables. The program works well but requires the database link table manager to run first every time the application starts. The programmer claims that is the only way it will run but with several part time employees working the chance for error is high. Is there a way to automatic link the tables as the Access application starts?
Thanks

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 1

You can have a startup form with a space for user name and password, and then on the click event of a button loop through the 10 tables and reconnect based on the existing connection string and the new user name and password, since it sounds like you're using SQL Security instead of Windows Integrated Security on your SQL database.

It would be something like this:

Form contains 1) text box: UsrName 2) text box: PWD, 3) command button "cmdOK"

Here's the code: Watch line breaks!

Private Sub cmdOK_Click()

Dim sSQL As String

Dim otbl As TableDef

Dim sMyConn As String

For Each otbl In CurrentDb.TableDefs

If IsNull(otbl.Connect) = True Or otbl.Connect = "" Then GoTo NextoTbl

'line begins with sMyConn and ends with Me.UsrName)

sMyConn = Replace(otbl.Connect, Mid(otbl.Connect, InStr(1, otbl.Connect, "UID="), InStr(InStr(1, otbl.Connect, "UID="), otbl.Connect, ";") - InStr(1, otbl.Connect, "UID=")), "UID=" & Me.UsrName)

'line begins with sMyConn and ends with Me.PWD)

sMyConn = Replace(sMyConn, Mid(sMyConn, InStr(1, sMyConn, "PWD="), InStr(InStr(1, sMyConn, "PWD="), sMyConn, ";") - InStr(1, sMyConn, "PWD=")), "PWD=" & Me.PWD)

If otbl.Connect <> sMyConn Then

otbl.Connect = sMyConn

otbl.RefreshLink

End If

NextoTbl:

Next otbl

End Sub

Thanks,

Dick Schroth Right there with Babe Points: 725 More actions · Answer 2

I think you need to find a new programmer. First of all, there is no reason why the tables should need to be relinked every time. And if you used an Access Project (.ade) instead of the .mdb there would not be any linking at all and you would have the capability of using more of SQL Servers features easily.

Dick

Frances L SSCarpal Tunnel Points: 4727 More actions · Answer 3

I used DSN to link the sql table in ACCESS. I do not know what is the disadvantage of that.

PhilM99 SSCrazy Points: 2214 More actions · Answer 4

It's true that it's not necessary to relink the tables each time, but it in practice, it might be a good idea because there's only 10 of them and that's the best way to catch errors caused by network downs, moved databases, programmer twiddles etc. Far better there than when the user starts doing some data entry. I use an ini file to externalize the location.

Note that each user likely has their own copy of the Access db and thus their links to the back end SQL server are their own (so each has to run the refresh).

Often the best way to run the refresh is to put it in the Form_load for the startup form.

I'd suggest listing the tables in a table kept only in the front-end db called 'linked_tables' or something like that and run through the recordset, linking each table according to information retrieved from the ini file.

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 5

Rich, I agree with Phil. I'd to a "linked_tables" table too, if it were my application. But, I don't see any need for an ini file, unless it is for an adp, as all the information can be kept within the local table (on the "front end").

I usually put in a few extra fields into my version of that table -- since I develop off-site, my path to Access datatables and my connection string to SQL server is different from my users. I put fields for both sets in the table, and then in the startup form loop through the tables and re-link each one based on username -- If me, then use one set, otherwise use the other set. It saves a lot of time and effort.

The code I posted earlier is a way to only relink the SQL tables that are currently linked -- it is more a patch to resolve your existing problem than an ideal, long-term solution, which is the "linked_tables" table concept.

Dick Schroth Right there with Babe Points: 725 More actions · Answer 6

It seems to me it makes a difference whether you will always only be using these 10 tables, or would eventually like to use all of them. Relinking 400+ tables takes an unnecessarily long time - with an ADP you can relocate all just with a change of connection string, and not have to waste all that time.

Dick

Metra Christofferson Ten Centuries Points: 1153 More actions · Answer 7

An ADP would only be of benefit here if ALL the tables used are from the same SQL database, there are no Jet tables in use, and no ODBC links to other database(s). It would also require a lot of code changes to what appears to be an otherwise working system. Sounds kinda expensive to me, for what could be a simple login form.