Connect Access to SQL Server

[RAG2007]

Hi,

I'm a new SQL Server user, and am having initial troule connecting my Access front end to my SQL Server tables in the back end. I have no problem doing it through ODBC on my computer, but on other users machines I get this error:

SQL State 28000
SQL Server error 18456
login failed

The only different between my machine and their's is that I have SQL Server Management Suite, and they have no SQL Server software loaded in. I assumed that they didn't need anything installed. What am I doing wrong?

Robin

[ck9663]

Hi,

I'm a new SQL Server user, and am having initial troule connecting my Access front end to my SQL Server tables in the back end. I have no problem doing it through ODBC on my computer, but on other users machines I get this error:

SQL State 28000
SQL Server error 18456
login failed

The only different between my machine and their's is that I have SQL Server Management Suite, and they have no SQL Server software loaded in. I assumed that they didn't need anything installed. What am I doing wrong?

Robin

try checking the setting of your sql connection. if you're developing in the same machine that you're sql server is, sometimes the server name is "(local)". that will not work once you deploy your application...i f you're using odbc, you might need to create an odbc connection on each machine that you install it to...try to test the connection of your odbc after creating to make sure you configured it properly...

-- ck

[Jim Doherty]

Hi,

I'm a new SQL Server user, and am having initial troule connecting my Access front end to my SQL Server tables in the back end. I have no problem doing it through ODBC on my computer, but on other users machines I get this error:

SQL State 28000
SQL Server error 18456
login failed

The only different between my machine and their's is that I have SQL Server Management Suite, and they have no SQL Server software loaded in. I assumed that they didn't need anything installed. What am I doing wrong?

Robin

Hi Rag,

I have some routines here (a simple strategy if you like) to create your odbc in code if they don't exist on the machine from the access frontend. ie when your main form opens a simple checkmark to denote (odbc ready, something like that) so that if the checkmark come up as unticked you'd know the routine failed and that odbc drivers not on their machine kind of thing). How are you connecting? windows integrated security? Have you looked at using ADP files (or is your app fully developed in mdb) ADP's they don't use odbc instead they perform under (udl) universal datalink which is integrated into the adp (access data project interface) for speaking directly to SQL server

Jim :)

[RAG2007]

Thanks for the tips. I've been thinking about using an ADP, but hadn't gotten to that point yet, maybe this is time to do so.

I'm connecting through ODBC, using SQL Server authentication and a login ID. I use the same login on my computer and a users, and on mine I can access, on theirs I cannot. The server is not local, but on our intranet.

Internal tech support said I should embed the SQL connection string into my front end. Do you think they mean using ADODB?

Thanks

Hi Rag,

I have some routines here (a simple strategy if you like) to create your odbc in code if they don't exist on the machine from the access frontend. ie when your main form opens a simple checkmark to denote (odbc ready, something like that) so that if the checkmark come up as unticked you'd know the routine failed and that odbc drivers not on their machine kind of thing). How are you connecting? windows integrated security? Have you looked at using ADP files (or is your app fully developed in mdb) ADP's they don't use odbc instead they perform under (udl) universal datalink which is integrated into the adp (access data project interface) for speaking directly to SQL server

Jim :)

[RAG2007]

I've made the ADP, and now am going through my vba code to adapt. I've been using DAO, not ADO (still unfamiliar with ADO), and I see the DAO isn't working anymore. Is ADO the language to use with ADPs then?

Hi Rag,

I have some routines here (a simple strategy if you like) to create your odbc in code if they don't exist on the machine from the access frontend. ie when your main form opens a simple checkmark to denote (odbc ready, something like that) so that if the checkmark come up as unticked you'd know the routine failed and that odbc drivers not on their machine kind of thing). How are you connecting? windows integrated security? Have you looked at using ADP files (or is your app fully developed in mdb) ADP's they don't use odbc instead they perform under (udl) universal datalink which is integrated into the adp (access data project interface) for speaking directly to SQL server

Jim :)

[Jim Doherty]

I've made the ADP, and now am going through my vba code to adapt. I've been using DAO, not ADO (still unfamiliar with ADO), and I see the DAO isn't working anymore. Is ADO the language to use with ADPs then?

If you are predominently working with recordsets in code yes use ADO.

If you NEED local tables ie on the client you might want to stick with your mdb format DAO and ODBC but if 'not' at least try out the ADP. I use ADP file nearly all the time to communicate with SQL Server they have form properties ie: inputparameters , resynch, serverfilter amongst others designed for working with SQL server. As for the connection embedded bit in your post? you will use ADODB provider for SQL Server driver with recordsets and yes you can hardcode it if you like as a string into your application if you want to open and close separate connections

Standard Security:
Provider=SQLOLE DB;Data Source=YourServ erName;Initial Catalog= YourDatabaseNam e;UserId=YourUs ername;Password =YourPassword;

Trusted connection:
Provider=SQLOLE DB;Data Source=Your_Ser ver_Name;Initia l Catalog=YourDat abaseName;Integ rated Security=SSPI;


Your ADP will have a persistent connection to the Server in the universal data link that is prebuilt with the ADP so referencing a connection in code for your recordset is a simple matter of stating

Dim conn as ADODB.Connectio n
Set conn= CurrentProject. Connection


Is there any reason why you cannot switch to windows integrated security if you are on a network your network knows who you are already when you logon to the network. SQL server can feed off that authenticate against it returning strings to your app like system_user
to use in any recordset data handling routines which in Access would equate to the CurrentUser function when working in a secure environment. In addition you wouldnt have to concern yourself with the password bit because thats built into the network arrangements... have look at the connection strings above...passwor d is not required on the integrated security connection string

Hope this helps

Jim :)

[RAG2007]

I think I'll go with the ADP method, thanks for the clarity on the connection strings. Taking a while to fix bugs now, though, as I have to make sure my old access sql strings are valid in sql server. I'm running into some other issues now with Access, subforms now coming up blank when previously they worked fine, and on some of them I get the nebulous error "Syntax error or access violation".

If you are predominently working with recordsets in code yes use ADO.

If you NEED local tables ie on the client you might want to stick with your mdb format DAO and ODBC but if 'not' at least try out the ADP. I use ADP file nearly all the time to communicate with SQL Server they have form properties ie: inputparameters , resynch, serverfilter amongst others designed for working with SQL server. As for the connection embedded bit in your post? you will use ADODB provider for SQL Server driver with recordsets and yes you can hardcode it if you like as a string into your application if you want to open and close separate connections

Standard Security:
Provider=SQLOLE DB;Data Source=YourServ erName;Initial Catalog= YourDatabaseNam e;UserId=YourUs ername;Password =YourPassword;

Trusted connection:
Provider=SQLOLE DB;Data Source=Your_Ser ver_Name;Initia l Catalog=YourDat abaseName;Integ rated Security=SSPI;


Your ADP will have a persistent connection to the Server in the universal data link that is prebuilt with the ADP so referencing a connection in code for your recordset is a simple matter of stating

Dim conn as ADODB.Connectio n
Set conn= CurrentProject. Connection


Is there any reason why you cannot switch to windows integrated security if you are on a network your network knows who you are already when you logon to the network. SQL server can feed off that authenticate against it returning strings to your app like system_user
to use in any recordset data handling routines which in Access would equate to the CurrentUser function when working in a secure environment. In addition you wouldnt have to concern yourself with the password bit because thats built into the network arrangements... have look at the connection strings above...passwor d is not required on the integrated security connection string

Hope this helps

Jim :)

[ck9663]

most write-ups and white papers are recommending mixed authentication as well. for ease of management and integrated security within the windows infrastructure. .

-- ck

[Jim Doherty]

most write-ups and white papers are recommending mixed authentication as well. for ease of management and integrated security within the windows infrastructure. .

-- ck

Spot on ck I should have mentioned that on the server side :) and RAG you won't be disappointed with the ADP so any effort 'will' be worth your while. Yes....... you'll get some rather tiresome issues smacking you in the face but they are and will be rectifiable and during the process you will doubltess see some "hmmmm nice! couldn't do that with an mdb" features.

Jim :)

[RAG2007]

I'm walking through it all now, close to finished with the ADP conversion. Didn't realize how much coding had to be changed for SQL Server and ADO, and how many little things I had to rewrite (all my FindRecords had to be reworked).

One question about security. To use windows integrated security, how do I set that up in the SQL Server? Do I need to create a new user with the windows username and passwords for every use? Or is there some easier way? I have like 25 users.

Robin

[ck9663]

I'm walking through it all now, close to finished with the ADP conversion. Didn't realize how much coding had to be changed for SQL Server and ADO, and how many little things I had to rewrite (all my FindRecords had to be reworked).

One question about security. To use windows integrated security, how do I set that up in the SQL Server? Do I need to create a new user with the windows username and passwords for every use? Or is there some easier way? I have like 25 users.

Robin

no you don't need to create each user. you just have to point which domain should the sql server get the account credentials from. you still have to grant all those users (DOMAIN\uname) with the necessary rights....

-- ck

[RAG2007]

Good, looking into that now.

Another question, I've based some forms and subforms on views, but am finding that views based on multiple tables cannot be updated. This creates a problem for my forms that are for data entry. Any suggestions for ways to get around that? In my mdb file I could create a recordsource based off of a query, I was getting errors when I did that in the adp.

R

no you don't need to create each user. you just have to point which domain should the sql server get the account credentials from. you still have to grant all those users (DOMAIN\uname) with the necessary rights....

-- ck

[ck9663]

Good, looking into that now.

Another question, I've based some forms and subforms on views, but am finding that views based on multiple tables cannot be updated. This creates a problem for my forms that are for data entry. Any suggestions for ways to get around that? In my mdb file I could create a recordsource based off of a query, I was getting errors when I did that in the adp.

R

i don't know about ADP. but an updateable view is a little tricky...if the error is saying you can not update the actual table, you just did not define the proper rights on the object...you have to specifically say that this user or group have the necessary rights on a db


-- ck

[Jim Doherty]

Good, looking into that now.

Another question, I've based some forms and subforms on views, but am finding that views based on multiple tables cannot be updated. This creates a problem for my forms that are for data entry. Any suggestions for ways to get around that? In my mdb file I could create a recordsource based off of a query, I was getting errors when I did that in the adp.

R

You can only edit the one side of a two table join. (and you define the 'Unique table' in the forms property for this purpose). As you realise a third table will result in an uneditable view

Look for examples of where you can embed 'user defined functions' within a view to achieve that dataset where you might otherwise have to rely on a join to a third table to return a particular column of data. Your view remains 'editable' in these circumstances.

Its hard to give you an example specifically for your needs without knowing the makeup of your system.

If you really get stuck on this PM me with your email (and timezone) and I will invite you to my desktop via remote software and visually demonstrate to you what I mean. I have forms that use views where the essence of the dataset returned by the view is comprised of multiple tables and involve embedded user defined functions. It isnt a workaround its a question of knowing how to build the view to achieve what you actually need.

Regards

Jim:)

[RAG2007]

Great info. I'm doing some research into it now... Chances are I'll take you up on the remote access, as I'm still learning to navegate SQL Server Management Studio.

You mention the "unique table" the forms property, in Access. I defined that but the recordset is still not editable. Do I have to look at how the View is constructed?

You can only edit the one side of a two table join. (and you define the 'Unique table' in the forms property for this purpose). As you realise a third table will result in an uneditable view

Look for examples of where you can embed 'user defined functions' within a view to achieve that dataset where you might otherwise have to rely on a join to a third table to return a particular column of data. Your view remains 'editable' in these circumstances.

Its hard to give you an example specifically for your needs without knowing the makeup of your system.

If you really get stuck on this PM me with your email (and timezone) and I will invite you to my desktop via remote software and visually demonstrate to you what I mean. I have forms that use views where the essence of the dataset returned by the view is comprised of multiple tables and involve embedded user defined functions. It isnt a workaround its a question of knowing how to build the view to achieve what you actually need.

Regards

Jim:)