Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using Transfer Database to link to a SQL Server table

Posted on 2004-08-05
3
Medium Priority
?
274 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

610 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