Solved

Linked table manager to remote MS SQL server

Posted on 2008-10-22
8
1,158 Views
Last Modified: 2013-11-17
I have a MS Access front end which is linking to a remote SQL server.  Within the forms are many drop down boxes.  The combo boxes use the "linked table manager" to link to the look-up tables.  Currently this has been set up by using the "linked table manager" to use the ODBC connection.  It results in a description like below:

ODBC;Description=Datasource for IMEX at Logan;DRIVER=SQL Server;SERVER=XXX-SERVER;APP=Microsoft Office 3;WSID=THINKPAD; DATABASE=IMEX_HQ; Network=DBMSSOCN; Trusted_Connection=Yes; TABLE=dbo.tblTransfers

But if I try to connect to the new server it fails.  I have tried using udl to devise a connection string but this seems to  exceed 255 characters:
[Provider=MSDASQL.1;Password=xxxx;Persist Security Info=True;User ID=uuuuuu;Extended Properties="Description=dynxxxxxwebsites_xxxxx;DRIVER=SQL Server;SERVER=123.123.123.123;UID=uuuuuu;PWD=xxxxxxx;APP=Microsoft Data Access Components;WSID=THINKPAD;Network=DBMSSOCN";Initial Catalog=dynxxxxxwebsites_xxxxx

Can anyone suggest how I can link these tables to the remote server.  Without having to to include statements in every combo box.

Incidentally the VBA coding behind the forms can use SQLOLEDB.1 to access the data.
0
Comment
Question by:Nigel Keith-Walker
  • 4
  • 4
8 Comments
 
LVL 84
Comment Utility
I wasn't aware you could input a connection string in a combo box ... perhaps you mean you have a combo based on a Query, and that query is a pass-through query?

Are you using linked tables? That is, from the Database window, in the Tables section, do you see Linked tables? If so, you can just base your combos on those tables.
0
 

Author Comment

by:Nigel Keith-Walker
Comment Utility
OK
The combo box has Property>data>row source type = table/query
The row source is Select agent,Country from tblagents
This will pick up the table from
tools>database  utilities>linked table manager
The table manager has the connection string
0
 
LVL 84
Comment Utility
So you're using linked tables ...

The "table manager" doesn't have your connection string, this is a property of the TableDef. Open any of your linked tables in Design view (you'll get a warning but just ignore it) and click View - Properties. You'll see the Connect property there. You can edit this property if need be, and it would be used for any object (query, combo, listbox, form, etc) which uses that table in its source.

So I'm not clear exactly what you're having trouble with ... are you prompted for a username/password? If so, you can store this information in the connection string, and typically you only have to do this one time.

You said this process fails ... do you receive an error? If so, post it.

0
 

Author Comment

by:Nigel Keith-Walker
Comment Utility
OK.  
when I enter into the table design it warns that Ithe table is a linked table with prperties that cannot be modified.  But I open anyway.  YES
It presents the table fieldames and data types
I select properties
The description contains details of the old server:  
ODBC;Description=Datasource for IMEX at Logan;DRIVER=SQL Server;SERVER=XXXXX-SERVER;APP=Microsoft Office 2003;WSID=THINKPAD;DATABASE=IMEX_HQ;Network=DBMSSOCN;Trusted_Connection=Yes;TABLE=dbo.tblPaycode

I want to change this to point to the new server.  Usually I would perform this from the "linked table manager" pointing this to an ODBC connection - but the connection fails.

What should I put into the descriptionfield to link that table to the new server?  It will not be a trusted connection
0
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 

Author Comment

by:Nigel Keith-Walker
Comment Utility
Playing around with the linked table manager:  there are now two entries for each table
e.g.
tblbank (database=IMEX_HQ)
and
imexfx_tblBank (datbase=DynamicWebsites_imexfx)

It also insisted that it had a key before allowing the new link to be set up.  A colleague working on a database at the same web hoster also indicated that he needed a key

if I go into the relationship behind each of the combo boxes, I can remove the link to the old table and replace with the link to the new table.  There are a lot of combo boxes.  Is there a better method?
0
 
LVL 84
Comment Utility
If you run the Linked Table Manager and choose the correct settings (i.e. make sure to choose the "Always prompt for a new Location" checkbox), and then choose the correct server/DSN ... does your connection string not save at this point?

You can always relink via code:
http://mvps.org/access/tables/tbl0010.htm
0
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 400 total points
Comment Utility
Access/SQL Server requires that you have a Primary Key on each table. There is no getting around this using Linked TAbles, and this is also solid database design.

You can rename your links to anything you like ... ASSUMING THIS IS A LINKED TABLE you can simply delete the tblBank you have now, and rename imexfx_tblbank to "tblbank" ... Access will automatically use that new table with your link.

If this is NOT a linked table (i.e. this is an ADP), then deleting the table will delete the table on the server. See the images below for more info
LinkedTables.jpg
ADPTables.jpg
ADPTAble-Properties.jpg
0
 

Author Closing Comment

by:Nigel Keith-Walker
Comment Utility
Of course I can delete the old table and rename the new table.  Thanks for your guidance.
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

11 Experts available now in Live!

Get 1:1 Help Now