distributing Access front ends

[gunner]

Hi all,



I'm fairly new to Access and trying to understand the big picture.

So, I have an MS SQL Server with all the data. I'm creating an Access file and connecting to that server using ODBC connection. Now I want distribute my access file to users who are not located on my network (i.e. they are at different locations). And I'm having the following problems:

1) Is it true that to distribute my application I need to install ODBC driver on all user computers and create an ODBC connection so that my access is able to connect to the sql server?
2) Is there another way of creating one access file and distributing it without any modifications on the end-user's computer?

Thanks!

[ajetrumpet]

ummm...without a driver, a connection can't be established. That's the point of an ODBC driver, sir.

[gunner]

ajetrumpet, thanks for your answer. I have another question to you, if you don't mind. What might be a reason for not being able to connect to a remote SQL server from access, but being able to connect through Sql Server Management Studio? Thanks

[ajetrumpet]

ajetrumpet, thanks for your answer. I have another question to you, if you don't mind. What might be a reason for not being able to connect to a remote SQL server from access, but being able to connect through Sql Server Management Studio? Thanks

I have no idea. I don't know anything about the latter. For Access, there's a multitude of reasons. I don't do much ODBC right now, but...what method are you using? When access starts, what happens?

(OBVIOUSLY, not having a driver on the machine would cause it)

[gunner]

I'm creating an ODBC connection string. And I'm not able to connect to the server with the same credentials that I am using with MS SQL Studio.

[pkstormy]

Hi all,

2) Is there another way of creating one access file and distributing it without any modifications on the end-user's computer?

Thanks!

You can also try a File DSN although I prefer using a System DSN. I will put code in my startup form to call a function which automatically creates/refreshes the ODBC System DSN. Here's the code I use to do this: https://www.accessforums.net/code-re...ally-7547.html

This has a module which will create an ODBC System DSN if it doesn't exist which is based on the linked tables (note: you'll need to edit the module function and change the 1 line of code to point to your SQL Server (ie. SQL Server = "xxxx").

Note though that if a user has Windows Vista or 7, if the UAC is turned on, this code won't work. For these users, the ODBC System DSN will need to be manually created (since typically an admin user would need to log on to create ODBC's if the user's have limited functions to update.)

If you can't successfully connect when creating an ODBC DSN (manually) on the specific user's computer, make sure that they can connect to the SQL Server drive and that they have permissions to the db on the SQL Server. You need to also make sure you're configuring the ODBC DSN correctly (ie. Windows Authentication or Mixed Authentication depending on how you configured SQL Server to accept connections.) When you get to the last step on the ODBC Configuration, there is a "Test Connection" button which tells you if it can connect successfully or not based upon what you entered for the servername/username/password.

If you can't even create an ODBC, make sure that you are able to create an ODBC System DSN based upon who's logged in (again, typically on Windows Vista/7 you'll need to log in as Administrator).

Also importantly, when you originally linked the SQL Server tables into your frontend mdb/accdb file, make sure you checked the checkbox "Save Password" on the linking form wizard (small checkbox on the bottom right side of the form where you selected the tables to link). Try deleting and re-linking the SQL Server tables in your frontend mdb/accdb just to make sure. This is typically one reason another user will have problems.

[pkstormy]

I'm creating an ODBC connection string. And I'm not able to connect to the server with the same credentials that I am using with MS SQL Studio.

When manually creating the ODBC System DSN on the user's computer, are you able to successfully connect using any credentials? (ie. when clicking the "Test Connection" button.) This might depend upon if you're configuring the ODBC System DSN to use Windows Authentication or Mixed Authentication. This is typically the reason.

[gunner]

pkstormy, thank you for your reply. I'm using server authentication to connect to the DB. Every time I create a DSN (user, system, file) I fail to connect to the server, but I am able to connect to the server using MS SQL Management Studio. That's weird, don't you think?

[pkstormy]

pkstormy, thank you for your reply. I'm using server authentication to connect to the DB. Every time I create a DSN (user, system, file) I fail to connect to the server, but I am able to connect to the server using MS SQL Management Studio. That's weird, don't you think?

What version of SQL Server are you using? Some versions of SQL Server (ie. Express version) have limited capability for external users to connect to that SQL Server. For example, the SQL Server 2000 (Standard Edition) doesn't allow any external connections (except if the SQL Server Enterprise Management client itself is installed on that computer and only connecting via the client) but the SQL Server 2000 (Enterprise Edition) allows unlimited external connections to it (note that you don't want to install the SQL Server client on everyone's computer though.)

If it is a SQL Server version that allows external connections and it is installed on your computer, are others able to see/connect to your computer?

[Max D]

Hm, thats strange. When we developed our enterprise DB, we linked Access to MS SQL in usual way: created ADP project file linked thru MS SQL datasource. We havn't used any manual connections, because there was no need really this way.

After that ADP file opens good on all our PCs and there is no any connection problems or ODBC manipulations.

SQL Management studio connects to SQL Server in other way, then regular SQL users and programs. This mean, it could happen, that you can connect via Studio, while regular data connections are disabled.

The is some politic option at SQL Server, that denies external connections. Find & switch it.

[pkstormy]

Personally, I'd ditch the ADP project and then design my application into a simple mdb file and then link the SQL Server tables. For me, ADPs are a headache to work with and I get the same speed results in connecting to the backend SQL Server using just an mdb and linking the SQL Server tables in it. It's much easier to work with an mdb file with linked SQL Server tables (I then just create an mde file for the users to utilize.)

Regardless, when you get to the 'Test Connection' button when configuring the System ODBC DSN and it says it doesn't succesfully connect, you'll need to look at the permissions on SQL Server (for the specific db) and compare that for the user you are logged in as along with how you are configuring the System ODBC DSN. The key point when configuring is when you select "With Windows NT authentication...." or "With SQL Server authentication...."

If you select SQL Security authentication, then try supplying the admin (sa) password for testing (you'll probably want to change this.)

What version of SQL Server and what version of MSAccess are you using? There have been some noted problems with ADP projects and SQL Server.

If though you've had other ADP projects and other users can successfully connect/utilize these, I'd guess that your problem is more related to how you're setting up the ODBC for permissions and if SQL Server authentication, the login/password your using. That compared to the users on SQL Server who have permissions to the actual db tables.

As a note (I wasn't sure if you knew this or not): the SQL Server managment studio doesn't need to be installed on each user's computer for them to use the ADP project.