Solved

Linked table manager to remote MS SQL server

Posted on 2008-10-22
8
1,161 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
ID: 22774860
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
ID: 22775023
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
ID: 22775114
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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 

Author Comment

by:Nigel Keith-Walker
ID: 22775271
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
 

Author Comment

by:Nigel Keith-Walker
ID: 22775606
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
ID: 22775618
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
ID: 22775675
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
ID: 31508669
Of course I can delete the old table and rename the new table.  Thanks for your guidance.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

825 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