Solved

Linked table manager to remote MS SQL server

Posted on 2008-10-22
8
1,168 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
8 Comments
 
LVL 85
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 85
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
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 

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 85
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 85

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

Online Training Solution

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action. Forget about retraining and skyrocket knowledge retention rates.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

696 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