?
Solved

Using Transfer Database to link to a SQL Server table

Posted on 2004-08-05
3
Medium Priority
?
268 Views
Last Modified: 2010-08-05
In the test copy of my front end mdb most of my linked SQL Server tables have the same connect string. A few of them have a different connect string that causes their links to fail when I copy the test front end to my production user's pc.

I noticed that the connect string that does not work on the production pc includes "WSID=PAULJMCNEIL" which would refer to my pc, not the production pc. I tried using the TransferDatabase command with the connect string that works and it links ok but it still puts "WSID=PAULJMCNEIL" in the string so I'm assuming the link will fail. Also is there a way in VBA to specify unique fields so as to eliminate the dialog box that comes up when a link to a SQL Server table is made?

This is the connect string that works:

Description=ICMS Production;DRIVER=SQL Server;SERVER=NTSQLCIP001;APP=Microsoft Office XP;WSID=3J16FTBZYDB4;Trusted_Connection=Yes

This is the one that fails when I copy the mdb to the production pc:

DSN=ICMS Reports db;Description=ICMS Reports db;APP=Microsoft Office XP;WSID=PAULJMCNEIL;Network=DBMSSOCN;Trusted_Connection=Yes
0
Comment
Question by:paulmcneil
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 18

Expert Comment

by:Data-Man
ID: 11732477
Could you edit the connection string to use his WSID instead of yours?

Did you know you don't have to use DSN's, you can have a DSN-less connection to SQL Server Tables...then you never have to worry about  those pesky DSN's again.
Do a search on EE using these key words

DSN Less "SQL Server"

Mike
0
 

Author Comment

by:paulmcneil
ID: 11744145
Mike,

I tried the following "DSN-less" connect string:

"DRIVER=SQL Server; Server=NTSQLCIP001; Databse=ICMS; APP=Microsfot Office XP; Netowrk=DBMSSOCN;Trusted_Connection=Yes"

I got err 3170 Could not find installable ISAM

I researched that err on Experts Exchange and the only somewhat clear lead I found was to check my registry for the presence of ISAM formats. My registry does indeed have many folders within the ISAM formats folder (which is within the Jet\4.0 path), including a folder named <SQL Database>. In the SQL Database folder the ExportFilter and ImportFilter items are both = ODBC Databases ().

Any insight into the ISAM err 3170 beyond what has been posted numerous times here would be appreciated. Thanks.
0
 
LVL 18

Accepted Solution

by:
Data-Man earned 2000 total points
ID: 11744218
Here is what my connection string looks like for my SQL Server tables.

ODBC;DRIVER=SQL Server;SERVER=MVISQL;APP=Microsoft Office XP;WSID=ServerName;DATABASE=dbNameNetwork=DBNMPNTW;Trusted_Connection=Yes


the word ODBC; still goes in the front of the connection string event though there is no DSN.

Mike
0

Featured Post

Enroll in August's Course of the Month

August's CompTIA IT Fundamentals course includes 19 hours of basic computer principle modules and prepares you for the certification exam. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question