Solved

Linked table manager to remote MS SQL server

Posted on 2008-10-22
8
1,159 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
 

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
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…

863 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

19 Experts available now in Live!

Get 1:1 Help Now