Access 2013 SQL login error to ODBC SQL database

Wrote a program in Access 2013.  32 bit.    Had ODBC connections (System)  named MaM     connected with  sql name 'test' and pw 'testpw'.        Accessed the data on the development computer.   Windows 7 Pro.         I installed the Access 2013 runtime program on a Windows 8.1 system.     Setup the 32 bit ODBC connection with the same as above.    When I try to use the program when it accesses the data from the SQL server via the ODBC connection it fails with login failure.    It refers back to my AD login on the Window 7 computer.   I don't know what I did wrong but I really need to fix the issue because I have ten other computers to get this to work on.  Right now it works on two Windows8.1 computers.   It just stopped on this one.   Any ideas?
patrickmillerAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

patrickmillerAuthor Commented:
It also says "login from untrusted domain and cannot be used with Windows Authentication"      I didn't use Windows auth.
Kelvin SparksCommented:
Generally in these sorts of issues, you may have clicked the Use trusted connection option (that implies Windows Authentication).

Personally, I use a dsn-less connection - no dsn to worry about - setup one database and distribute - no connection setup needed on each machine - its ready to go.

You do need VBA to start with to do the connections.


Kelvin
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's possible to post a screen shot of the ODBC configuration?
And also how are you connecting from Access?
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

patrickmillerAuthor Commented:
How do I use VBA to setup the connections?
Kelvin SparksCommented:
You need to create a local Access table (called ODBC connections for instance) that lists all the tables in SQL Server (TableName_SQLServer), and the name you want them to appear as in Access (TableName_Access). Then I set a constant being the connection string. Such as
Const strConnect = "ODBC;Driver={SQL Server Native Client 11.0};Server=ServerName;Database=DatabaseName;UID=UserName;PWD=password"

or if you are using SQL Server authentication

Const strConnect = "ODBC;Driver={SQL Server Native Client 11.0};Server=ServerName;Database=DatabaseName;

I ten use this code to connect - just run it once the connection string is set.

Public Function RelinkSQLTables()
On Error GoTo EH

Dim db As DAO.Database
Dim tdf As DAO.TableDef
Dim strTblServer As String
Dim strTblLocal As String
Dim rsTables As DAO.Recordset
Dim sSQL As String

Set db = CurrentDb()

sSQL = "SELECT * FROM ODBCTables where Not IsNull(TableName_SQLServer);"

Set rsTables = db.OpenRecordset(sSQL, dbOpenSnapshot)
Do Until rsTables.EOF
    For Each tdf In db.TableDefs
        If tdf.Name = rsTables!TableName_Access Then
            ''Build the dsn-less connection string
            db.TableDefs.Delete rsTables!TableName_Access
            Exit For
        End If
    Next
        Set tdf = db.CreateTableDef(rsTables!TableName_Access, dbAttachSavePWD, rsTables!TableName_SQLServer, strConnect)
        db.TableDefs.Append tdf
    rsTables.MoveNext
Loop

MsgBox "All SQL tables relinked", vbOKOnly + vbInformation, "Process Complete"

Exit_Sub:
    Exit Function
   
EH:
    MsgBox Err.Number & ": " & Err.Description
   
    Resume Exit_Sub

End Function

Kelvin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
patrickmillerAuthor Commented:
I have never written a Public Function.    Where would I put this function for it to run?
Kelvin SparksCommented:
In the modules part of the database (you are using the desktop version of Access 2013?)

Kelvin
Kelvin SparksCommented:
In the toolbar, use the create menu and select New Module


K
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.