Solved

Using Transfer Database to link to a SQL Server table

Posted on 2004-08-05
3
264 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 500 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

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

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.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

724 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