• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 280
  • Last Modified:

Using Transfer Database to link to a SQL Server table

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
paulmcneil
Asked:
paulmcneil
  • 2
1 Solution
 
Data-ManCOOCommented:
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
 
paulmcneilAuthor Commented:
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
 
Data-ManCOOCommented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now