Connecting Access ADP user to SQL server 2000

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Joe - 27 Jan 2004 04:36 GMT

I've created a Access adp database. As administrator I can log in and run DB, but the user I added "JT" as a "power user" cannot connect to the tables. The Access adp opens, but I get the message "MS Office Access could not log on to the server. Verify that the log on information is correct."

The Access ADP will open showing the forms but not the tables. When I test the connection of the Access adp as "JT", I get the message "Test connection failed because of an error intializing provider. Log in failed for user JT" If I test the connection as "Administrator" it says "connection succeeded.

I have the SQL server database set to "Windows authentication mode", and I have no problems logging on to the server as user "JT". I've checked & double checked all securtiy settings & user permission settings for SQL server & the database and JT is in there with the appropriate permissions.

I've read the help sections on logging on with Windows Authentication & as Server passwords. They don't seem to explain my predicament
What should I do next
I'm a newbie to SQL Server 2000 & not a computer programer. Please give simple answers

Reply to this Message

SFAxess - 27 Jan 2004 22:52 GMT

Do you have the Connection property set in the
Access .ADP?
This can be set from the File>Connection dialog in Access.
Take a look at those properties and make sure you have
the correct server, selected "Use Windows NT Integrated
Security", and have selected the correct database.

>-----Original Message-----
>I've created a Access adp database. As administrator I can log in and run DB, but the user I added "JT" as

a "power user" cannot connect to the tables. The Access
adp opens, but I get the message "MS Office Access could
not log on to the server. Verify that the log on
information is correct."

>The Access ADP will open showing the forms but not the tables. When I test the connection of the Access adp

as "JT", I get the message "Test connection failed
because of an error intializing provider. Log in failed
for user JT" If I test the connection
as "Administrator" it says "connection succeeded."

>I have the SQL server database set to "Windows authentication mode", and I have no problems logging on

to the server as user "JT". I've checked & double
checked all securtiy settings & user permission settings
for SQL server & the database and JT is in there with the
appropriate permissions.

>I've read the help sections on logging on with Windows Authentication & as Server passwords. They don't seem to

explain my predicament.

>What should I do next?
>I'm a newbie to SQL Server 2000 & not a computer programer. Please give simple answers.
>
>.

Reply to this Message

MGFoster - 27 Jan 2004 22:57 GMT

Just a guess....

If you have access to the SQL'r Enterprise Manager (EM) open the Users
Logon section & double-click on the user JT in the right-pane of the
EM. A dialog box will appear with tabs across the top. Click the one
on the right (I can't remember it's name) and click on the database
name in the top pane and in the bottom pane click the "grant access"
(or something like that) check box.

Or, ...

After opening the .adp open an Immediate (debug) window (Ctrl-G) and
type something like this (all one line):

CurrentProject.Connection =
"Provider=Microsoft.Access.OLEDB.10.0;Persist Security Info=False;Data
Source=ServerName;Integrated Security=SSPI;Initial Catalog=DBName;Data
Provider=SQLOLEDB.1"

You'll have to substitute your Data Source and Initial Catalog names.
Hopefully you have OLEDB version 10. If not, find out the name of
your db provider & substitute.

MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

> I've created a Access adp database. As administrator I can log in and run DB, but the user I added "JT" as a "power user" cannot connect to the tables. The Access adp opens, but I get the message "MS Office Access could not log on to the server. Verify that the log on information is correct."
>
[quoted text clipped - 5 lines]
> What should I do next?
> I'm a newbie to SQL Server 2000 & not a computer programer. Please give simple answers.

Reply to this Message