Hi guys
Following on from an issue a couple of years back, now archived: http://www.thescripts.com/forum/thre...f.connect.html
I'm having the same problems.
I can update the Querydef.connec t property programmaticall y without any problems, however if I try to update the Tabledef.connec t property it won't do it, even though it runs through the code successfully.
When I try to manually update the connect column in MsysObjects it won't let me do it, gives a "Control Can't be Edited; It's Bound to Replication System Column Connect" message in the control bar, so I'm guessing this is related...
Any help on this would be appreciated.
FYI the code is below, and we do not use in-built Access database security on our Access dbs (could this be why it won't let me edit it through code?) -
Dim db As DAO.Database
-
Set db = CurrentDb
-
-
'Reset the connection property for all pass through queries for DB1 database only
-
-
For Each QueryDef In db.QueryDefs
-
If QueryDef.Connect Like "*DATABASE=DB1*" Then
-
QueryDef.Properties("Connect") = gblConnectionString
-
QueryDef.Properties.Refresh
-
End If
-
Next
2 5329
OK I think I might be on to something here
"For Database objects, new Connection objects, linked tables, and TableDef objects not yet appended to a collection, this property setting is read/write. For QueryDef objects and base tables, this property is read-only."
This seems to suggest that because a querydef is opened then closed I can edit the connection, but because a connection is open to an ODBC linked table it is seen as a base table and therefore read-only... so maybe I need to close the connection before updating? Hmmm.
Also I missed a section of the code snippet... trying this one... -
Dim db As DAO.Database
-
Set db = CurrentDb
-
-
Dim strTableName, strSourceTableName As String
-
Dim objTableDef As TableDef
-
-
'Reset the connection property for all pass through queries for DB1 database only
-
-
For Each QueryDef In db.QueryDefs
-
-
If QueryDef.Properties("Connect") Like "*DATABASE=DB1*" Then
-
QueryDef.Properties("Connect") = gblConnectionString
-
QueryDef.Properties.Refresh
-
End If
-
-
Next
-
-
'Reset connections for linked tables for DB1 database only
-
For Each TableDef In db.TableDefs
-
If TableDef.Connect Like "*DATABASE=DB1*" Then
-
TableDef.Properties("Connect") = gblConnectionString
-
TableDef.Properties.Refresh
-
End If
-
Next
-
-
End Function
nico5038 3,080
Recognized Expert Specialist
Hmm, not sure or the LIKE will work as expected, why not try for your tables: - For Each TableDef In db.TableDefs
-
If TableDef.Connect <> "" Then
-
TableDef.Properties("Connect") = gblConnectionString
-
TableDef.Properties.Refresh
-
End If
-
Next
-
Thus all tables with a connection will be reconnected and the Msys (read-only) tables skipped.
Nic;o)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Chetan |
last post by:
I have setup an ODBC driver for my MySQL database and use a 3rd party
program to connect to it. Everytime the software connects, the ODBC
driver setup screen pops up. I have to click OK on it to continue. I
set the parameter "Dont prompt on connect" in the options but this has
not solved the issue.
The system setup is Win2K SP2, MySQL 4.0.15, MySQL ODBC driver 3.51.
Any help will be appreciated.
|
by: Philip Wright |
last post by:
I am trying to connect to a DB2 v7.2 database from Lotus 123 v9.8
on a Windows XP Pro machine. I have the Software Development Client
installed on the XP machine and have run the Client Configuration
Assistant and registered the SAMPLE and my TEST databases with the
ODBC Administrator as System DSN data sources. Everything seems
fine, but when I try to connect to "External Tables" through 123
my DB2 databases are not listed under available...
|
by: Pradeep |
last post by:
DB2ers,
Our Windows application connects to any DB2 server using the ODBC
driver.
It seems there are two ODBC drivers that IBM provides - DB Connect and
DB2 Runtime Client. We have tested our product well with DB2 runtime
client and it seems to work great. However, one of our customers is
using DB Connect 7.1 as the ODBC driver. Although our product seems to
be able to connect to the server, query execution behavior is
|
by: mcbill20 |
last post by:
Hello all. I just installed Oracle 10g developer tools on a machine
running XP Pro and Office XP. Before this I had just the Oracle 9
client installed. I the previous configuration, I was able to access
any of the Oracle tables on another machine but now I am having
problems. Unfortunately, I don't remember the correct syntax for the
ODBC connect string and I am hoping that is my whole problem.
I am trying to connect to an Oracle 9...
|
by: paul_tomlin |
last post by:
We connect via terminal services to an excel spreadsheet that has an
ODBC link to an access database using a pivot table, when we try to
refresh the data within the excel spreadsheet we receive the following
error "the microsoft jet database engine cannot find the input table or
query....."
this works ok if the user connects to terminal services as the
administrator however if they connect as any other issue it won't work
Anyone got...
| |
by: Jean |
last post by:
Hi everyone,
I was hoping somebody could assist me in this issue. I am quite a
newbie to ODBC connections and was struggling to search for related
topics. Here is my situation:
I have a Access 2000 .mdb file which has several linked tables to a
ODBC data source, namely an Oracle back-end. I have already setup the
connctions to these back-end tables in the ODBC Administrator, and it
works fine. When I view one of these tables for the...
|
by: Bob Sanderson |
last post by:
I am trying to set up an ODBC DSN to our company MySQL database so that I
can use a MS Access front end. I am using the MySQL ODBC 3.51 driver. The
database is running off of Apache on our Windows 2000 server (call it
'foobar'). The host is 'localhost', the user is 'root'. To access the
database via a web browser on a PC on our network, I use the address
'http://foobar:8080/". This works fine. If I set up a DSN on the server,
I connect...
|
by: Greg Strong |
last post by:
Hello All,
The short questions are
1 Do you know how to make DSN connection close in Access to Oracle 10g
Express Edition?
&/or
2 Do you know how to make a DSN-less pass-through query work from
|
by: powerfulhosting |
last post by:
I'm trying to allow someone from a specific IP to connect to a mySQL db
w/OBDC. I've run several GRANT queries on the mysql db i.e.
GRANT select,update,insert,delete,create,drop,alter,index on ESdemo.*
to 'aname'@'24.55.41.125' identified by 'pwd232123';
GRANT select,update,insert,delete,create,drop,alter,index on
ESdemostore.* to
'aname'@'santamonica-cuda4-24-55-40-1.vnnyca.adelphia.net' identified
by 'pwd232123';
|
by: Dale Sampson |
last post by:
My app uses an ODBC connection for a .mdb file. I want the app to create the
source if it does not already exist (I copy a blank .mdb file to the users
data store if it doesn't exist).
I see how to do this using SQLConfigDataSource() and that works ok. Thing
is, how to handle if the source already exists.
Is there a way to check & see if the source already exists?
Alternately, if it does exist & I call SQLConfigDataSource() does it...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
| |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
|
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |