Solved Problem Connecting to SQL Server

[Spalle]

Hello Everyone,

I am currently facing an issue with the ODBC connection to my SQL Server. While it functions perfectly on my personal computer, it encounters difficulties on my coworkers' PCs.
They have successfully set up the ODBC connection on their machines, and creating a new database and connecting, viewing and editing the data poses no problem. However, they are unable to establish a connection with the frontend.
The error message displayed is shown in the attached image. Upon clicking "OK," they encounter the SQL Server Registration Popup (refer to image 2).
I am hopeful that someone may be familiar with this issue.

thanks in advance

 

[Gasman]

Think you would need to at least translate it?

 

[Spalle]

Yes of course,
It says:
Error while connecting
SQLState: S1000
SQLServer Error:0
Microsoft ODBC SQL Server Driver(Cannot generate SSPI Context

i think it is this error on english

 

[tvanstiphout]

This basically means: I cannot login to the SQL Server using your Windows credentials. There can be several causes. Search online, such as this page: Cannot generate SSPI context when connecting to SQL Server - SQL Server | Microsoft Learn

 

[Spalle]

Alright, I've managed to find a workaround for the error. I believe the issue stemmed from my connection to the database using Microsoft Authentication, whereas my coworkers used a password. To address this, I created a loop that iterates through all tables, deletes them, and then recreates and reconnects them. This approach has proven effective.

However, a challenge has surfaced. I utilize db.TableDefs (after initializing db = CurrentDb()), but it doesn't provide a complete list of all tables in the Access Frontend. Some tables are missing, while others appear duplicated. I'm unsure about what might be incorrect in my use of db = CurrentDb().

 

[xavier.batlle]

However, a challenge has surfaced. I utilize db.TableDefs (after initializing db = CurrentDb()), but it doesn't provide a complete list of all tables in the Access Frontend. Some tables are missing, while others appear duplicated. I'm unsure about what might be incorrect in my use of db = CurrentDb().

Do you assign or reassign Set db=CurrentDb() after reconnecting SQL server tables?

 

[Spalle]

I can show you the code

 

[xavier.batlle]

I can show you the code

I think the problem is that in your For Each tdf In dbs.Tabledef loop, you delete the tabledef (dbs.TableDefs.Delete) before you create the new link ,and when you delete an item all tabledef elements in dbs are renumbered. Because of this, it seems there are missing and duplicated tables.
You should try a different approach, for example:
- tblDef.RefreshLink instead of deleting and creating the link
Or create a loop from the max value to 0 like this:

    For I = M_Db.TableDefs.Count - 1 To 0 Step -1
        If Len(M_Db.TableDefs(I).Connect) Then
            M_Db.TableDefs.Delete M_Db.TableDefs(I).NAME
           ' Link the table
            ----
        
        End If
    Next I

 

[Spalle]

Yes that was the Problem, thank you