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
-
17 2949 NeoPa 32,568
Recognized Expert Moderator MVP
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.
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 14,534
Recognized Expert Moderator MVP 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 32,568
Recognized Expert Moderator MVP
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?
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 14,534
Recognized Expert Moderator MVP
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.
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 14,534
Recognized Expert Moderator MVP
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 32,568
Recognized Expert Moderator MVP
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.
Sign in to post your reply or Sign up for a free account.
Similar topics |
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 my
Python Code I use threads to communicate over the network and stuff
like this. Compilation and linking are working very well under Windows
and Linux with the same code. Under the sgi, UNIX machine some errors
occur and I don't no why....
|
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 oracle
library. I use the following command while linking:
ld -L(oracle lib path) -lnetv2 -lnttcp -lnetwork -lncr -lnetv2 -lnttcp
-lnetwork -lclient -lcommon -lgeneric -lmm -lnlsrtl3 -lcore4
For each element starting with -l has...
|
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 normal functions in
header files results in multiple definition errors even when include guards
are used.
--
STH
Hatton's Law: "There is only One inviolable Law"
KDevelop: http://www.kdevelop.org SuSE: http://www.suse.com
Mozilla:...
|
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 server tasks by
continuously iterating until it figures out the correct library order.
Recently we introduced some C++ legacy libraries. I am completely new
to C++ linking. Is there some tutorial that can explain the nuances of
C++ linking? We do...
|
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 references to functions and
objects not defined in the current translation. All such translator output
is collected into a program image which contains information needed for
execution in its execution environment."
What I'm wondering is what exactly...
| |
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 that is used
to connect to other databases and generate reports. below is sample
code of how the database does the linking i hope i give you enough
info to help me but if not let me know and i will give more.
Sub txtShipDataFileSub()
Dim...
|
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 (.lib)".
My question:
Should the linker incrementally link when I make a change to one of the
..cpps in one of my .lib projects?
For VC6 the answer is yes.
|
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 out.
Short answer: I should never have expected incremental linking to work.
Short answer addendum: Linking is slower in 7.1/.net/2003 than VC++6.
===
|
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 using the /MT
compilation option. Our debug solution's /MTd flag works fine and
using /MD also seems to work ok. For some reason I can't fathom, when
I use /MT, linking the static excutable completely dies (problems
resolving symbols in the STL,...
|
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 how can we control the type of linking
Hi any one please help me to clarify my doubt
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
| |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |