Converted DB .ADP to .ACCDB ODBC connection error on other users machines mine works

[cleteh]

I have been building rebuilding a .adp db into .accdb database. The original .adp version opens and runs with no issues on everyone's machine.

On my machine the .accdb version opens and works fine. Once another user opens the .accdb database and tries to open a form or a table they get this error: ODBC--connection to 'RiskControlDataBase' failed.

[HiTechCoach]

I have been building rebuilding a .adp db into .accdb database. The original .adp version opens and runs with no issues on everyone's machine.

On my machine the .accdb version opens and works fine. Once another user opens the .accdb database and tries to open a form or a table they get this error: ODBC--connection to 'RiskControlDataBase' failed.


First and very important:
It is critical that the front end is NOT shared. Does each user have their own copy of the front-end?

Second:
Have you installed the latest SQL Server Native client on each workstation?

Linking Tables:

Are you setting up a DSN and linking to the tables using it?

If yes, you will need to configure the DSN on each user's profile. Or switch to a DSN-Less connection in the deployed copy of the front end.

What type of authentication, Windows or SQL Server?


Here is what I do about the connection:

During development, I use a DSN connection.

Deploying front end to each user:
1) Compile the front end to a accde
2) Open the accde and run some VBA code that converts the connection to a DSN-Less connection.
3) Deploy the accde with the DSN-Less connection.

Here is the code I I use to convert the connection:

Using DSN-Less Connections (Click Here) (http://hitechcoach.com/component/weblinks/weblink/128-microsoft-servers-back-office/43-microsoft-sql-server/333-using-dsn-less-connections?Itemid=247)


TIP: A front end compiled into a .accde is more stable with errors that are not trapped (no error handling code).

[cleteh]

I was able to correct this issue today by searching for data connections then adding SQL Server to each individuals machine. The previous version was .adp and I'm converting to .accdb. I'm wondering what the reasoning is why the front end should not be shared? It was shared as a .adp and we had no problems. Just curious to the reasoning because I have no knowledge on this. Yes, every workstation has the latest sql native client installed.

[HiTechCoach]

Thanks for the update.

Glad to hear you have it working.


The new ACE (.accdb) format and Acess 2007 and later don't like two users opening the same database at the same time. Especially if it is not compiled to a .accde.

I have blogged on the Best Practices of splitting a database and deploying the front end: Access Apps and data - splitting (Click Here) (http://hitechcoach.com/microsoft-office/access/63-split-database)