Linking to SQL

[Nitro]

I'm having problems getting my DB to Link to different SQL servers. I got it to work fine on my own machine, but when distributed it at the client, and to another office, the Link didn't work. It either timed out, or gave errors.

I created a LinkTables Method (below) that would allow me to distribute the DB to the client running on another server. I created a local Settings table to store the Connection Strings of different locations. I wanted to either use a DSN file, or connect completely through code. I didn't want to setup System/User ODBC connections on anybody's machine. Unfortunately, I wasn't the one testing it out, but for some reason the tech could not get connected to SQL, without setup up a System ODBC manually. That's the reason I changed to ADP, because it was easier to connect.

Anyway, here's my code, and Test Connection String. Anyone have comments why this would not work? Also, what is required in the Connection String?

Thanks.

Public Sub LinkTables()
Dim DBPath As String
Dim DBPath2 As String
Dim TCPath As String
Dim tds As TableDefs
Dim td As TableDef
Dim x, y, p As Integer
Dim s, c As String
 
	On Error GoTo Error
	DBPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
 
	On Error Resume Next
	If gLocation = "" Then Exit Sub
	c = DLookup("ConnStr", "Settings", "ID = '" & gLocation & "'")
	If c = "" Then Exit Sub
	On Error GoTo Error
 
' Test Connection
c = "ODBC;DRIVER=SQL Server;UID=user;PWD=pass;SERVER=CORPMAT;DATABASE=AuditTest"	
 
	Set tds = CurrentDb.TableDefs
	y = tds.Count
	For x = 0 To y - 1
		Set td = tds(x)
		s = td.Connect
		If Left(s, 4) = "ODBC" Then ' Check for SQL Link
'		If Left(s, 9) = ";DATABASE" Then ' Check for MDB Link
			td.Connect = c
			td.RefreshLink
		End If
	Next
	MsgBox "Tables Linked Successfully."
leave:
	Set tds = Nothing
	Set td = Nothing
	Exit Sub
Error:
	MsgBox "Error Linking Tables! " + Error$, vbCritical
	Resume leave
End Sub

[NeoPa]

I don't know if you noticed but the DATABASE clause in your connection string is set to :

"ODBC;DRIVER=SQ L Server;UID=user ;PWD=pass;SERVE R=CORPMAT;DATAB ASE=A uditTest"

The spaces in 'A uditTest' look suspicious.

[Nitro]

That's just a typo from the copy/paste. It's not that way in my code.

This code works fine on my local machine, so I don't see why it wouldn't onsite. Actually I'm going their tomorrow to personally try it out. Until I do there's not much more info I can give, ie. Error Messages etc.

One main difference is I'm running MSSQL 2K locally on my Win XP desktop. Onsite they are running MSSQL 2K on a deticated server, and connecting through a network.

Can anyone think of a reason why Access wouldn't connect to SQL using this Test Connection String, assuming the Server, Database, Username and Password are correct? Could there be settings, security or otherwise on the server that's preventing it?

Also, what about WSID, APP and other options in the connection? Are any of those necessary?

Thanks for your time

[MMcCarthy]

Standard Security:
"ODBC;Driver={S QLServer};Serve r=CORPMAT;
Database=AuditT est;Uid=Your_Us ername;Pwd=Your _Password;"

Trusted connection:
"ODBC;Driver={S QLServer};Serve r=CORPMAT;
Database=AuditT est;Trusted_Con nection=yes;"

[NeoPa]

As you're using Standard security, have you made sure that the account details you've used on your local MS SQL Server are mirrored correctly on the networked one?

[Nitro]

As you're using Standard security, have you made sure that the account details you've used on your local MS SQL Server are mirrored correctly on the networked one?

Standard Security as opposed to what? Do you mean the account details of the User I'm logging on with? I just setup a standard user and gave it admin rights to the database, the same as I did on my local SQL.

I have some more info though.

Yesturday I went to another office running SQL on a server. I was able to Link the tables from a workstation using my method no problem. I used a User I setup the same as on my local.

Today my partner is onsite at the client, and having problems. It works fine when linking to the SQL DB while on the server, as I would expect. However, from a workstation it doesn't. Funny thing is it seems to link some of the tables, but not all. The ones that are not linking are looking for a TRUSTED CONNECTION. When he tries to open the table, it gives the Error:
COnnection faled :
SQLstate : '28000'
SQL Server Error : 18452
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user '(NULL)'.
Reason 'Not assoccated with a trusted SQL Server Connection'

Then it asks for a username and password, which he puts in, then it works.

Why would a few tables (at the end) be linked with a trusted connection, and the rest using the UID and PWD? My link method SHOULD be linking all tables the same, I would think.

Any ideas? I'm at a loss.

[MMcCarthy]

Did you see my previous post?

Standard Security as opposed to what? Do you mean the account details of the User I'm logging on with? I just setup a standard user and gave it admin rights to the database, the same as I did on my local SQL.

I have some more info though.

Yesturday I went to another office running SQL on a server. I was able to Link the tables from a workstation using my method no problem. I used a User I setup the same as on my local.

Today my partner is onsite at the client, and having problems. It works fine when linking to the SQL DB while on the server, as I would expect. However, from a workstation it doesn't. Funny thing is it seems to link some of the tables, but not all. The ones that are not linking are looking for a TRUSTED CONNECTION. When he tries to open the table, it gives the Error:
COnnection faled :
SQLstate : '28000'
SQL Server Error : 18452
[Microsoft][ODBC SQL Server Driver][SQL Server] Login failed for user '(NULL)'.
Reason 'Not assoccated with a trusted SQL Server Connection'

Then it asks for a username and password, which he puts in, then it works.

Why would a few tables (at the end) be linked with a trusted connection, and the rest using the UID and PWD? My link method SHOULD be linking all tables the same, I would think.

Any ideas? I'm at a loss.

[Nitro]

Did you see my previous post?

Now I did. Thanks.

Here is the connection string I'm using:
"ODBC;DRIVER=SQ L Server;UID=audi tuser;PWD=audit user;SERVER=COR PMAT;DATABASE=A uditTest"

I have a Login "audituser" setup in SQL with a pasword "audituser" , with Admin rights to the database "AuditTest" . The same way I have setup locally on my machine, and in the other office that worked. BTW, I also tried the "sa" login with the same results.

The two main questions are:
Why would ALL of the tables link normally while on the server?
Why would SOME of the tables link, but not others while on the workstation?
ie. Why would all the tables not use a Standard Connection instead of a Trusted?

As you can see in my code, I'm setting all of the tables to the same string. This just makes no sense to me.

Thanks for all the replies BTW. This has just been really fustrating, something that should be simple is giving me so many problems.

[MMcCarthy]

Adrian

You know more about this than I do, can I leave it with you? Or do you want me to try to get someone else involved?

Mary

Now I did. Thanks.

Here is the connection string I'm using:
"ODBC;DRIVER=SQ L Server;UID=audi tuser;PWD=audit user;SERVER=COR PMAT;DATABASE=A uditTest"

I have a Login "audituser" setup in SQL with a pasword "audituser" , with Admin rights to the database "AuditTest" . The same way I have setup locally on my machine, and in the other office that worked. BTW, I also tried the "sa" login with the same results.

The two main questions are:
Why would ALL of the tables link normally while on the server?
Why would SOME of the tables link, but not others while on the workstation?
ie. Why would all the tables not use a Standard Connection instead of a Trusted?

As you can see in my code, I'm setting all of the tables to the same string. This just makes no sense to me.

Thanks for all the replies BTW. This has just been really fustrating, something that should be simple is giving me so many problems.

[NeoPa]

I'm not at work atm so I have nothing to reference for this, and I haven't done any SQL Server setup work for a while (due mainly to my Access commitments), however, I'll try to dig up something from my memory.
We have a very structured domain system and so, for me, it is easier to ally my security to the security of the domain (Trusted Connection). From what I've learned of your, more varied and distributed, environment, I think the path you've chosen is more appropriate.
You need to :
Make sure that any connections use the account / password provided.
Make sure that the security mode for the SQL Server is set to Standard (or maybe better) Mixed mode.

Maybe also you can go to your development environment and test in STANDARD mode only. It's possible that it is set up in mixed mode and that it works because it ignores the username passed - as it knows it doesn't need it because you are an Admin on the server or domain of the server. Or something along similar lines - Just a thought.

If this doesn't throw any light on the matter, try making a backup of your db on the working (development) server and then restoring it (and attaching) to the server that doesn't work. Obviously you'll have to make a backup of the db on that machine first - you don't want to lose anything - you're just testing for info. If that works then you know that the db hasn't been set up EXACTLY as it should be.

No-one ever said setting up SQL Server was simple.
Using it when it's been properly set up by someone else should be.
Good luck with your search.

[Nitro]

Hello again,

Both, my local and onsite SQL Servers are set to "SQL Server and Windows" (Mixed Mode). However on my local I have my Windows User setup in SQL, so I'm able to use Trusted Login. Onsite they don't have Windows users setup, so Trusted Logins will not work.

I disabled my Windows User in SQL, so now I can no longer use my Trusted Login. I verified by setting up a new ODBC, and it forces me to enter a Username and Password.

Here's the weird part. When I set the links using the connection string, I go back and look at the connection string for each table, the UID and PWD are missing.

So,

I changed the connection string to: "ODBC;DRIVER=SQ L Server;SERVER=( local);DATABASE =Audit",
removing the UID and PWD. When I link the tables on my local, it works. Somewhere the UID and PWD are being stored. The question is where.

Any idea's?

Thanks

[NeoPa]

When you say you disabled the windows user, do you mean you set the Server to use STANDARD Mode only?
If not, you should.
Where do you use your connection string (in code or in a linked table or even elsewhere)?

[Nitro]

When you say you disabled the windows user, do you mean you set the Server to use STANDARD Mode only?
If not, you should.
Where do you use your connection string (in code or in a linked table or even elsewhere)?

I mean I just Disabled my Windows User in SQL, so the Trusted Login no longer works.

Where do set the Server to use STANDARD Mode only?

I'm using the Connection String in code. See my LinkTables Method.

[NeoPa]

If you administer your SQL server, select your server, then properties & it's on the main (first) page I think (I'm not at work now till Tuesday so have to go from memory).

[Nitro]

UPDATE:

I finally got the LinkTables method to work onsite, however I had to use a DSN file. I copied it to a common location on the server, and linked using it. Everyone who accesses the database should have access to the DSN file.

However, I'm still baffled as to why the link doesn't work within code using the Connection String and Refresh method. Has anyone had any luck doing it this way, and would like to share their secret.

Thanks for all the help though.