SQL Server <=> MS Access Project

[isoquin]

Hello-

This field is new for me, and I've looked elsewhere for tutorials.

I was recently given an SQL Server (IP) Address, username, and password. I need to work with the data in Access, and don't quite know how to get the two to speak to one another.

I've made a new project from existing data, and got to the Data Link properties. It's clear where the username and password goes, and I took a guess that the IP address goes in the "Select of enter server name" box. In fact, testing the connection with those gave me the following error:
"Test connection failed because of an error in initializing provider. Login failed for user 'myUserName'."

I didn't know if I was doing something completely wrong or not, but putting in a bogus address gave me:
"Test connection failed because of an error initializing provider. [DBNETLIB][ConnectionOpen (Connect()).]SQL
Server does not exist or access denied."

This leads me to believe I put the server address in the right place, but for some reason I'm still stuck on the outside. Am I doing something wrong? Or is my username/password incorrect?

[Jim Doherty]

Hello-

This field is new for me, and I've looked elsewhere for tutorials.

I was recently given an SQL Server (IP) Address, username, and password. I need to work with the data in Access, and don't quite know how to get the two to speak to one another.

I've made a new project from existing data, and got to the Data Link properties. It's clear where the username and password goes, and I took a guess that the IP address goes in the "Select of enter server name" box. In fact, testing the connection with those gave me the following error:
"Test connection failed because of an error in initializing provider. Login failed for user 'myUserName'."

I didn't know if I was doing something completely wrong or not, but putting in a bogus address gave me:
"Test connection failed because of an error initializing provider. [DBNETLIB][ConnectionOpen (Connect()).]SQL
Server does not exist or access denied."

This leads me to believe I put the server address in the right place, but for some reason I'm still stuck on the outside. Am I doing something wrong? Or is my username/password incorrect?

You don't say what version of SQL server you are using or connecting to but in brief there are two methods for authenticating against an SQL Server login one is using standard windows integrated security and the other is using SQL servers own built in security. (integrated security will be SQL servers acceptance of your windows login to validate your connection whereas SQL servers 'own' security is where you are logging in directly to the SQL server itself using a user name stored on the SQL server together with a password. IN EITHER case you must have an account on the SQL server itself as a user who is entitled to gain access to the SQL server. If your impression is that just using a datalink will give you access then you will mistaken (if I have perceived your post correctly). You need to speak with whoever it is who administers the SQL server machine to make sure you have a user account name on the SQL server itself

If you are on a networked computer in a domain connecting to another computer on which SQL server is hosted then any 'named' instances of standard installations of SQL Server 'should' appear in the dropdown on the datalink connection tab. (if it has been setup correctly). The same applies if the SQL server is mounted on your own machine of course.

If you do not see a named instance of SQL server in that dropdown then it could be any number of reasons but without knowing more about your set up the complexities are such ie is it on mapped drive/share at logon, or a VPN client ,server communication protocols in place etc etc

You are trying to access the SQL server using an ADP project file using datalink but which seems to be giving you a connection problem currently.... as an alternative try seeing if you can connect using an MDB file using ODBC as the connection method see if that gives issues (my own preference is ADP by the way so good choice :) but be ware MDB is favoured it seems for Access 2007

Jim :)

[isoquin]

You don't say what version of SQL server you are using or connecting to but in brief there are two methods for authenticating against an SQL Server login one is using standard windows integrated security and the other is using SQL servers own built in security. (integrated security will be SQL servers acceptance of your windows login to validate your connection whereas SQL servers 'own' security is where you are logging in directly to the SQL server itself using a user name stored on the SQL server together with a password. IN EITHER case you must have an account on the SQL server itself as a user who is entitled to gain access to the SQL server. If your impression is that just using a datalink will give you access then you will mistaken (if I have perceived your post correctly). You need to speak with whoever it is who administers the SQL server machine to make sure you have a user account name on the SQL server itself

If you are on a networked computer in a domain connecting to another computer on which SQL server is hosted then any 'named' instances of standard installations of SQL Server 'should' appear in the dropdown on the datalink connection tab. (if it has been setup correctly). The same applies if the SQL server is mounted on your own machine of course.

If you do not see a named instance of SQL server in that dropdown then it could be any number of reasons but without knowing more about your set up the complexities are such ie is it on mapped drive/share at logon, or a VPN client ,server communication protocols in place etc etc

You are trying to access the SQL server using an ADP project file using datalink but which seems to be giving you a connection problem currently.... as an alternative try seeing if you can connect using an MDB file using ODBC as the connection method see if that gives issues (my own preference is ADP by the way so good choice :) but be ware MDB is favoured it seems for Access 2007

Jim :)

Jim-

I greatly appreciate the reply. The SQL server is completely remote (as in, not on this computer, and not anywhere on my domain/LAN). I was just given an IP address for it. I was also given a specific username and password setup by the admin of that SQL server.

I also don't quite understand what MDB file using ODBC is. I've used access when building DBs from scratch, but I've never done *this* before, and as such I really need broken down instructions.

At the moment I'm somewhat of the belief that the username and password given to me might be incorrect (by the steps I mentioned in my first post).

[Jim Doherty]

Jim-

I greatly appreciate the reply. The SQL server is completely remote (as in, not on this computer, and not anywhere on my domain/LAN). I was just given an IP address for it. I was also given a specific username and password setup by the admin of that SQL server.

I also don't quite understand what MDB file using ODBC is. I've used access when building DBs from scratch, but I've never done *this* before, and as such I really need broken down instructions.

At the moment I'm somewhat of the belief that the username and password given to me might be incorrect (by the steps I mentioned in my first post).

Yes thats what I think could quite easily be the case too. I rather guessed that given you have an IP address and a user name and password that the sql server wouldnt be on your computer. you'd know about it :) I tried to reply generically in effect to cover a potential range.

The connectivity aspect should be a relatively simple process with UDL ('universal data link' as its called) is merely a text file saved with a udl file extension on your pc that is used to provide the necessary details to connect in line with the method for connection.

A description of that connectivity feature which is the standard built into the ADP format is described numerously on the web but heres a couple of examples here

http://msdn2.microsoft.com/en-us/library/aa224776(sql.80).aspx

and here

http://www.prezzatech.com/kb/articles/kb-1011-database_connection_strings.asp

Connection to SQL server and the ability for you to 'speak' too it comes in a couple of formats in Access you can either use the ADP Project file which is what you are doing, or a standard Access MDB file. This is where, if you chose the MDB method you would be setting up an ODBC connection string in the Control panel of your PC (Start..setting s...control panel...Adminis trative tools...Data Sources) selecting the SQL server ODBC driver and entering a user name and password for a connection which would again be stored on your pc and used to connect to SQL Server by the MDB file.

You can sample this yourself... if you create a simple mdb database and then open it up you would have no tables. (You have no need for them essentially because the tables are stored in SQL server) you merely would link to any tables by 'Attaching' them to the tables interface screen in Access where they would appear with an icon of a the world globe. You can then query and act on those tables as though they were physically present in your local MDB file database. You can throw the data into visible tables stored locally and so on (something you can't do with ADP files.)

To all intents and purposes you'd be forgiven for thinking the tables were in Access as a newbie but they are not in actual fact. Try it out.. when in the database window of an mdb file go to the menubar ..File...Get External Data....Files of type dropdown select ODBC databases ...then select the ODBC connection you created earlier in control panel (and if you didnt you can create one). Access will then ask you if you want to save the connection string in the database. Click Yes and you then see the tables attached as mentioned.

I won't go into the differences between ADP and MDB because basically you could write a small book on it. Suffice it to say they are different, having connectivity features and benefits inherent in one and not the other and vice versa. Most of what you want to do can be done in either of them.

MS favoured ADP files when they were first introduced for communicating with SQL server and to a large extent one could quite easily understand how people invested substantial time programming them using ADO. now MDB files are back in favour again (research Access 2007 you'll understand why) thus making DAO flavour of the month again (as if it ever wasn't... it is powerfully native of Access)

I have only mentioned all of this to make you 'aware', given you are 'newly' connecting with I suspect a 'blank interface' any extra knowledge might give you a clearer understanding of whether or not you have selected the interface 'suitable' for your purpose (whatever that might be long term). None of us know whether MS intend to drop or retain ADP in any future release forewarned is forearmed... so to speak.

Hope this informs you just that 'little' bit better (as for the specific issue currently lets see what your admin person says as to your user name and password it may as you point out simply be wrong)

Regards

Jim :)

[Rghertner]

Additional question

Hi,

I'm building an ADP for a client. The ADP that I used was linked to SQL Server 2005 Express on my machine, and when I sent them the ADP, they're having trouble connecting it to the server. They're SQL Server is also 2005 Express, and it's on another machine in the office. They have the IP Address (I can only assume it's correct, though maybe it's not), and have created a username and password.

They can make the connection when the ADP is on the same computer as SQL Server, but with the ADP on another machine, they cannot. They stick the IP address with \SQLEXPRESS in the "Select or enter server name" box, and enter the other info (username and password login, database name), and they get that same error message, "Server does not exist or access denied. Since the username/password is correct, and SQL Server is up and running fine, I'm thinking it's either the IP Address they've got is wrong, or the computer with SQL Server isn't open to the network (I don't know much about networking servers, so I don't really know the next step on that one). Any advice on how to know if the latter is the case? Am I missing something else?

Robin