Alternate way to connect Frontend 2

[ssatech]

Since upgrading our backend from Access to SQL Server (Windows Server 2000)with Access frontend for the past 2 years we currently experience a constant amount of ODBC call fail errors. We are currently setup using Windows XP for each workstation (12-15 users).I was just curious as we never had this issue when Access was used as a backend.

I have researched these errors in length but have not been able to find a resolution. Our management have grown impatient and are pondering moving away from Access.

I was just curious to know if they is an alternate way to connect our FE Access to SQL server other then using ODBC? or possibly the best practice to connect an Access FE to a SQL server BE.

 

[dhookom]

Have you created DSNs on all workstations or are you using DSN-less connections. Doug Steele has some code for using the later at

http://www.accessmvp.com/DJSteele/DSNLessLinks.html.

When connecting to the SQL Server, I have recently found that including the PORT number in the connection string (or in the DSN) has helped tremendously. Rather than specifying only the server name, add a comma followed by the port number which is usually 1433 but may be set up differently on your server.
Typical Connection: [blue]ODBC;DRIVER=sql server;SERVER=MyServer,1433;UID=xxxx;APP=Microsoft Office 2003;DATABASE=MyDB;Trusted_Connection=Yes;TABLE=dbo.tblUsers[/blue]

Duane
Hook'D on Access
MS Access MVP

 

[jsteph]

ssatech,
The below is what I do in this situation and it has given me no problems.

It does require a very simple two-field local table, which simple stores the list of tables to be linked, an the connection string. In reality if the conn. string is the same, this needn't even be stored.

I haven't put all variable dims in this example, so for example the strMach is the computer-name retreived from either an environ() or an api.

All you need for a connection string is:
ODBC;DSN=mydsn;uid=someuser;pwd=somepwd

The password can be left out and concatenated in code if you don't want to store it in a local table. In this example, it's a very public readonly dsn so I just store the pwd, but you can hide it in code, even a dll if you want a slightly tighter lid on it. I concat a few bits of info to the conn. string in the code to identify the machine and the app that the connection has because otherwise when you need to kill a connection you see dozens of "Microsoft Access" and nothing else, so you don't know who's who without digging into the sysinfo tables.

Sub Recon()
Set rsTB = db.OpenRecordset("Select * from tblTables WHERE trim('' & ODBCRelink) <> ''") 
rsTB.MoveFirst
Do Until rsTB.EOF
    strN = Trim("" & rsTB!tblname)
    'see if exists
    On Error Resume Next
    Set td = db.TableDefs(strN)
    If Err.Number <> 0 Then
        Err.Clear
    Else
        'exists, so delete it
        DoCmd.DeleteObject acTable, strN
    End If
    Set td = db.CreateTableDef(strN)
    strCnBase = Trim("" & rsTB!odbcrelink) 'base string
    strCnBase = strCnBase & ";APP=DWH_" & strMach & ";WSID=" & strUser & ";TABLE=dbo." & strN & "" 'I add this to give dba more info on who's doing what
    td.Connect = strCnBase
    td.Attributes = 131072 ' !! Important-->131072=dbAttachSavePWD
    td.SourceTableName = strN
    db.TableDefs.Refresh
    db.TableDefs.Append td
    If Err.Number <> 0 Then
        x = MsgBox("failed.  Keep going?", vbOKCancel, "Failed")
        If x = vbOK Then
            'ok, keep trying
        Else
            Exit Sub
        End If
        Err.Clear
    End If
    rsTB.MoveNext
Loop
db.TableDefs.Refresh
End Sub

 

[ssatech]

Duane, Jsteph,

Thanks for the feedback - it was valuable information I did not find specifically relating to ODBC call fails with Access and SQL server. I will try both methods and see what the outcome presents.

Please excuse my ignorance as I am fairly new to the Access FE and SQL Server BE. The upgrade was done a few years ago before my time.

But the following connection string in your example and code:
"ODBC;DRIVER=sqlserver;SERVER=MyServer,1433;UID=xxxx;APP=Microsoft Office 2003;DATABASE=MyDB;Trusted_Connection=Yes;TABLE=dbo.tblUsers" AND "Sub Recon()".

Would this be placed in a Module of the FE?

 

[jsteph]

ssatech,
Yes, that would all be in the frontend. The backend has data-tables only, and you populate tblTables, in the frontend, with the names of the tables in the backend.

In the frotnend you can have local tables, such as 'slush' tables, report results tables, etc, but that's probably another topic.

if the string you have in the example doesn't work, start with the simplest form:
ODBC;dsn=dsnname;uid=username;pwd=password

Then you can add on to that to get more detailed once you get the basics working.
--Jim