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

Answer 1

The code is getting the local name from:

 sLocalName = rs!TABLE_SCHEMA & "_" & rs!TABLE_NAME

But you do not have to include rs!TABLE_SCHEMA & "_" &, which is where it is getting "dbo_".

So

 sLocalName = rs!TABLE_NAME

In fact, the local name can be more or less anything you fancy.