Solved

Using Transfer Database to link to a SQL Server table

Posted on 2004-08-05
3
249 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
  • 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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now