MS Access forms Denied connection to SQL Express Backend

Question

I have a MS Access 2007 front end and SQL Server Express back end. (This was originally upsized from the Acces Database).

This database has been copied from Production and being run in a different location/domain to the production server. This is used for development. What i have are two connection strings so I can toggle between server/databases.

I connect the MS Access link tables through an ODBC connection string, using a SQL authentication to connect.

The SQL user has dbowner rights/access to the database

The link tables refresh OK and I can open the table and modify data within the tables in the front end but when I try to open a standard Access form, most of the are linked directly to the table, I get "Connection Failed..... Server does not exist or Access is Denied". Then a SQL Server logon box appears with the orignal server name, not the server the tables are currently connected to.

Here is the connection string for your reference: { Dim sLocalName As String Dim tdf As TableDef Dim rs As dao.Recordset

''This is a basic connection string, you may need to consider password and so forth
' cn = "ODBC;DSN=aid_dev;Trusted_Connection=No;APP=Microsoft Office 2007;DATABASE=aid_dev;"
cn = "ODBC;DRIVER=SQL Server;SERVER=KAL1\SQLEXPRESS;DATABASE=aid_dev;Uid=User;Pwd=Password"

''All fields fro`enter code here`m tables
strSQL = "SELECT TABLE_CATALOG, " _
& "TABLE_SCHEMA, " _
& "TABLE_NAME, " _
& "TABLE_TYPE " _
& "FROM [" & cn & "].INFORMATION_SCHEMA.tables " _
& "WHERE TABLE_TYPE = 'BASE TABLE'"

Set rs = CurrentDb.OpenRecordset(strSQL)

Do While Not rs.EOF
    sLocalName = rs!TABLE_SCHEMA & "_" & rs!TABLE_NAME

    With CurrentDb
        If DLookup("Name", "MSysObjects", "Name='" & sLocalName & "'") <> vbNullString Then
            If .TableDefs(sLocalName).Connect <> cn Then
                .TableDefs(sLocalName).Connect = cn
                .TableDefs(sLocalName).RefreshLink
            End If
        Else
            ''If the table does not have a unique index, you will neded to create one
            ''if you wish to update.
            Set tdf = .CreateTableDef(sLocalName)
            tdf.Connect = cn
            tdf.SourceTableName = rs!TABLE_NAME
            .TableDefs.Append tdf
            .TableDefs.Refresh

            ''This will produce a message box if the table does not have a unique index
            ''DoCmd.TransferDatabase acLink, "ODBC Database", cn, acTable, rs!TABLE_NAME, sLocalName
        End If
    End With
    rs.MoveNext
Loop}

Thanks in Advance

Roger

EDIT - Discovered issue but now how to deal with it I Discovered once I make the connection it actually creates the links to the SQL tables, but as dbo.Tablename. This now breaks all the reports as the forms do not point to dbo.tablenames but just table names. The original links still exist with the same original connection property.

Either 1) how do I change the original tables connection properties or 2) how do i rename the dbo.tablename to tablename

or do i just change every form query etc.......

Help this is sending me mad.

Thanks

Roger

Why don't you just use the linked tables as a source for your form ? There are plenty of routines on the net to update the source of linked tablesincluding this one: upsizing.co.uk/Art2_RelinkingTablesAndViews.aspx — Patrick Honorez

HK1 HK1 · Accepted Answer · 2012-08-18T14:44:58

Here's a function I use to change the table names. I run this on startup:

Public Sub subChangeLinkedTableNames()

    Dim dbCurr As DAO.Database
    Dim tdfCurr As DAO.TableDef

    Set dbCurr = CurrentDb()

    For Each tdfCurr In dbCurr.TableDefs
        If Len(tdfCurr.Connect) > 0 Then
            If Left(tdfCurr.Name, 4) = "dbo_" Then
                tdfCurr.Name = Replace(tdfCurr.Name, "dbo_", "")
            End If
        End If
    Next


    Set tdfCurr = Nothing
    Set dbCurr = Nothing

End Sub

I've went to a different method now. Every time the database application loads, I delete all the existing linked tables and re-establish them. I've devised my own method where I use an ADO connection to connect to SQL Server which loads an ADO recordset using my table called _LinkedTables. This table contains the information needed to create every link including the real table name and the LinkAs name (which allows me to use a different name to link to a table, helpful for things like Views). I also have a field that contains Primary Key field name(s).

The information needed to establish the initial ADO connection to SQL Server is contained in a local, static table in my frontend file called something like tblDatabase. Here I have connection strings for both ADO and ODBC. I have even written it so that I can use a different database for development versus production.

This all isn't really that advanced as much as it's a web of related tables and code that is too much to post here.

Some folks just refresh the links each time the database loads, which works fine. I had a very specific reason that I went with deleting and relinking each table and I cannot remember exactly what it was. I think it had to do with the fact that I was working on the database a lot and I wanted changes to take affect ASAP even on existing front-end files (in case I made an error). Instead of only relinking existing table links I wanted the ability to have the user close out, reopen, and link to tables that were not linked just a second ago. But that really is kind of a fringe case and quite possibly overkill that causes quite a bit of extra load time. If you have 20 tables it's ok. If you have 200 it probably wouldn't be a good idea.

MS Access forms Denied connection to SQL Express Backend

3 Answers