By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,197 Members | 1,173 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,197 IT Pros & Developers. It's quick & easy.

Linking to SQL

P: 10
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.

Expand|Select|Wrap|Line Numbers
  1. Public Sub LinkTables()
  2. Dim DBPath As String
  3. Dim DBPath2 As String
  4. Dim TCPath As String
  5. Dim tds As TableDefs
  6. Dim td As TableDef
  7. Dim x, y, p As Integer
  8. Dim s, c As String
  9.  
  10.     On Error GoTo Error
  11.     DBPath = Left(CurrentDb.Name, InStrRev(CurrentDb.Name, "\"))
  12.  
  13.     On Error Resume Next
  14.     If gLocation = "" Then Exit Sub
  15.     c = DLookup("ConnStr", "Settings", "ID = '" & gLocation & "'")
  16.     If c = "" Then Exit Sub
  17.     On Error GoTo Error
  18.  
  19. ' Test Connection
  20. c = "ODBC;DRIVER=SQL Server;UID=user;PWD=pass;SERVER=CORPMAT;DATABASE=AuditTest"    
  21.  
  22.     Set tds = CurrentDb.TableDefs
  23.     y = tds.Count
  24.     For x = 0 To y - 1
  25.         Set td = tds(x)
  26.         s = td.Connect
  27.         If Left(s, 4) = "ODBC" Then ' Check for SQL Link
  28. '        If Left(s, 9) = ";DATABASE" Then ' Check for MDB Link
  29.             td.Connect = c
  30.             td.RefreshLink
  31.         End If
  32.     Next
  33.     MsgBox "Tables Linked Successfully."
  34. leave:
  35.     Set tds = Nothing
  36.     Set td = Nothing
  37.     Exit Sub
  38. Error:
  39.     MsgBox "Error Linking Tables! " + Error$, vbCritical
  40.     Resume leave
  41. End Sub
  42.  
Nov 14 '06 #1
Share this Question
Share on Google+
17 Replies


NeoPa
Expert Mod 15k+
P: 31,494
I don't know if you noticed but the DATABASE clause in your connection string is set to :
"ODBC;DRIVER=SQL Server;UID=user;PWD=pass;SERVER=CORPMAT;DATABASE=A uditTest"
The spaces in 'A uditTest' look suspicious.
Nov 15 '06 #2

P: 10
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
Nov 15 '06 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
Standard Security:
"ODBC;Driver={SQLServer};Server=CORPMAT;
Database=AuditTest;Uid=Your_Username;Pwd=Your_Pass word;"

Trusted connection:
"ODBC;Driver={SQLServer};Server=CORPMAT;
Database=AuditTest;Trusted_Connection=yes;"
Nov 15 '06 #4

NeoPa
Expert Mod 15k+
P: 31,494
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?
Nov 15 '06 #5

P: 10
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.
Nov 16 '06 #6

MMcCarthy
Expert Mod 10K+
P: 14,534
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.
Nov 16 '06 #7

P: 10
Did you see my previous post?
Now I did. Thanks.

Here is the connection string I'm using:
"ODBC;DRIVER=SQL Server;UID=audituser;PWD=audituser;SERVER=CORPMAT; DATABASE=AuditTest"

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.
Nov 16 '06 #8

MMcCarthy
Expert Mod 10K+
P: 14,534
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=SQL Server;UID=audituser;PWD=audituser;SERVER=CORPMAT; DATABASE=AuditTest"

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.
Nov 16 '06 #9

NeoPa
Expert Mod 15k+
P: 31,494
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.
Nov 16 '06 #10

P: 10
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=SQL 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
Nov 17 '06 #11

NeoPa
Expert Mod 15k+
P: 31,494
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)?
Nov 17 '06 #12

P: 10
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.
Nov 17 '06 #13

NeoPa
Expert Mod 15k+
P: 31,494
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).
Nov 18 '06 #14

P: 10
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.
Nov 21 '06 #15

NeoPa
Expert Mod 15k+
P: 31,494
I'm afraid we use System DSNs set up on each machine.
Nov 21 '06 #16

P: 10
I'm afraid we use System DSNs set up on each machine.
So you go t every User's Machine and setup a System DSN? Isn't that a hassle?
Nov 21 '06 #17

NeoPa
Expert Mod 15k+
P: 31,494
It's part of the PC build instructions.
Only users who use the ODBC links have it enabled.
We only have 50-60 users in the office.
BTW - I wasn't suggesting it as a solution for you - that would be stupid, as I don't know enough about your environment and what you're trying to achieve.
Nov 21 '06 #18

Post your reply

Sign in to post your reply or Sign up for a free account.