473,322 Members | 1,510 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Linking to SQL

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
17 2928
NeoPa
32,556 Expert Mod 16PB
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
Nitro
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
14,534 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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
Nitro
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
14,534 Expert Mod 8TB
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
Nitro
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
14,534 Expert Mod 8TB
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
32,556 Expert Mod 16PB
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
Nitro
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
32,556 Expert Mod 16PB
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
Nitro
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
32,556 Expert Mod 16PB
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
Nitro
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
32,556 Expert Mod 16PB
I'm afraid we use System DSNs set up on each machine.
Nov 21 '06 #16
Nitro
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
32,556 Expert Mod 16PB
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

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

Similar topics

0
by: Wolfgang | last post by:
I have a problem with linking my CPP Code under a irix6 machine (sgi, UNIX). In my CPP code I use some Functions which are written in Python. So its a kind of CPP wrapper for my Python functions In...
0
by: Dibyendu Roy | last post by:
Hi All, I build an object called "dblorcle" to connect to oracle database in Sun solaris box. This is built linking with various oracle ".a" (archived, for static linking) files come with standard...
7
by: Steven T. Hatton | last post by:
Is there anything that gives a good description of how source code is converted into a translation unit, then object code, and then linked. I'm particularly interested in understanding why putting...
2
by: sunil | last post by:
Hi, We have lot of c and fortran archive libraries that have complex dependencies. We have different server tasks that use some of these libraries. We have developed a tool inhouse that links...
20
by: Steven T. Hatton | last post by:
I just read this in the description of how C++ is supposed to be implemented: "All external object and function references are resolved. Library components are linked to satisfy external...
0
by: gasturbtec | last post by:
please help im new at access programming and i just got this project dropped in my lap because the old programmer quit. i've been doing ok so far but now i need to add code to an existing database...
6
by: Rudy Ray Moore | last post by:
I work with a multi-project workspace. One project (the "startup" project) has a "Configuration Type" of "Application (.exe)". The other 40 projects have a "Configuration Type" of "Static Library...
0
by: Rudy Ray Moore | last post by:
I've been having trouble getting incremental linking to work under Visual C++ .net 2003 7.1 for my multi-project workspace. Ronald Laeremans and Carl Daniel (and a few others) helped me figure it...
0
by: Philip Lowman | last post by:
I am in the process of trying to migrate a couple of build solutions to Visual Studio Express 2005 from VS 2003 Professional and I am running into a weird C/C++ runtime library linking issue when...
1
by: srikar | last post by:
what is the difference between static linking & dynamic linking, what are the advantages of each? How to perform static linking & Dynamic linking by using gcc -o liniking will be done , but...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.