Access and ODBC Link to SQL 2000

Question

Access and ODBC Link to SQL 2000

Sharon Williams

Grasshopper

Points: 24
More actions
November 21, 2006 at 2:51 pm

#67816

I recently moved my databases to SQL 2000 from Access 2000. Users are still using Access as their front-end. I am finding that the ODBC connection does not "stay" when another user accesses the database. I keep having to use the Linked Table Manager to reconnect. What can I do to keep everyone's link connected? HELP!
-Sharon Williams

Viewing 14 posts - 1 through 13 (of 13 total)

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

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 1

Strange one this. Rather than using the Linked table manager - create the ODBC links thru the ODBC DSN and tick the Save Password option.

[File] [Get External Data] [Linked Files] type [ODBC] [System DSN] point to DSN and tick save password

The above works for me.

Richie

Sharon Williams Grasshopper Points: 24 More actions · Answer 2

Richie,

Initially, that is how the database was linked, but when I need different users to access the database through MS Access, the connector needs to be made for each inidividual. It keeps getting lost, and that is what I need help with.

-Sharon

Jeff Rivera SSC Journeyman Points: 95 More actions · Answer 3

Are you using System DSN or User DSN? We use System DSN and rarely have this problem. Additionally when our Access app starts up we pop up a splash screen which behind the scenes refreshes the ODBC/DSN connection.

Jeff

Sharon Williams Grasshopper Points: 24 More actions · Answer 4

Jeff, how can i re-create the pop-up you are talking about???

Sharon

Jeff Rivera SSC Journeyman Points: 95 More actions · Answer 5

Sharon,

Here is the code we use:

Public Sub RefreshDSN(strDSNName As String, strDatabaseName As String, Optional frmStatus As Form)

Dim tdftemp As TableDef

Dim strConnect As String

On Error GoTo ErrLog

Open "c:\RefreshLog.txt" For Output As #1

If Not frmStatus Is Nothing Then

frmStatus.pbStatus.Min = 0

frmStatus.pbStatus.Max = CurrentDb().TableDefs.Count

frmStatus.pbStatus.Value = 0

End If

For Each tdftemp In CurrentDb().TableDefs

If Left(tdftemp.Connect, 4) = "ODBC" Then

strConnect = "ODBC;DSN=" & strDSNName & ";APP=Microsoft® Access;DATABASE=" & strDatabaseName & ";Trusted_Connection=Yes"

tdftemp.Connect = strConnect

tdftemp.RefreshLink

End If

If Not frmStatus Is Nothing Then

frmStatus.pbStatus.Value = frmStatus.pbStatus.Value + 1

DoEvents

End If

Next tdftemp

Close #1

ExitSub:

Exit Sub

ErrLog:

MsgBox "An error has occurred. Refresh will continue. Please check log file."

Print #1, tdftemp.SourceTableName

Resume Next

End Sub

Sharon Williams Grasshopper Points: 24 More actions · Answer 6

Thanks!!! I am totally going to try this....now for a really dumb question: Where do I put this?

-Sharon

Jeff Rivera SSC Journeyman Points: 95 More actions · Answer 7

Well, one way to do this is to go under Tools, Startup in Access. You can specify a form to popup. Then put this code in your form and trigger it by the OnOpen event. The result will be when you open your Access MDB this code will run.

Jeff

Sharon Williams Grasshopper Points: 24 More actions · Answer 8

Thanks for your help Jeff...another question though..

When I put it in the form to run, am I using the expression builder, macro builder or code builder or another way? Sorry if I sound ultra ignorant...it's all new to me

Sharon

David Tooker Right there with Babe Points: 746 More actions · Answer 9

Why not just change the database to a Access project that connects directly to SQL?

Sharon Williams Grasshopper Points: 24 More actions · Answer 10

The tables are connected to SQL, but the forms, reports and queries my users need remain in Access. The problem is that the SQL connection, keeps disconnecting when multiple users are involved.

-S

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 11

Sharon,

Jeff was asking earlier whether you used System or User DSN's - I use System.

The following functions will help with linked tables:

The removedbo will rename a linked table dropping the dbo_ and it then operates similar to an Access table.

The refreshODBC will refresh tables that were previously linked and also updates for changes made in SQL structures.

As in Jeffs item you place the Refreshodbc function in the OnOpen method of a form that is set to run at startup - [Tools] [Startup] [Display Form/Page] formname.

Sub RemoveDBO()

Dim tbl As TableDef

For Each tbl In CurrentDb.TableDefs

If Len(tbl.Connect) > 0 Then

tbl.NAME = Replace(tbl.NAME, "dbo_", "")

End If

MsgBox "Remove Complete"

End Sub

Function RefreshODBC() As Collection

Dim tdf As TableDef, db As Database

Set db = CurrentDb

db.TableDefs.Refresh

For Each tdf In db.TableDefs

With tdf

If Len(.Connect) > 0 Then

If Left$(.Connect, 4) = "ODBC" Then

' Debug.Print tdf.Connect

' tbl.Connect = Replace(tbl.Connect, "RTSERVER", "Ross28\ross")

tdf.RefreshLink

End If

End With

Set db = Nothing

MsgBox "Refresh Complete"

End Function

Sharon Williams Grasshopper Points: 24 More actions · Answer 12

Thanks...you guys are being extremely helpful. I know I sould like a complete dolt, but where in the text do I put my connector info in? I'm thinking in:

tbl.Connect = Replace(tbl.Connect, "RTSERVER", "Ross28\ross")

I've never had to do something remotely close to anything like this, so pretend (ha) you are working with a total newbie.

Oh, and I use System DSN also.

Sharon

Richard O'Sullivan Ten Centuries Points: 1118 More actions · Answer 13

Sharon - that commented out bit was for changing server.

OK - in any sub place either or both commands and by calling that sub they will execute.

Alternatively - press Ctrl-G and you will open the Immediate window

Refreshodbc and press enter and the function will be called.

Richie