Hello Everyone,
I was wondering if anyone knows of a way to add a code that would automatically refresh link tables upon opening of database. This is needed due to fact that sometimes split forms wouldn't open due to a lagg and would freak out the user with the error.
The fix was to refresh linked tables manually.
THanks./
-AF
8 36704 jforbes 1,107
Recognized Expert Top Contributor
This might be what you are looking for: http://bytes.com/topic/access/insigh...bles-using-vba
I think I used NeoPa's example above to come up with this: - Public Function RefreshLinks(ByVal sDatabase As String) As Boolean
-
On Error GoTo ErrorOut
-
-
'Refresh table links to a backend database
-
-
Dim dbs As Database
-
Dim tdf As TableDef
-
Dim sCurrentTDF As String
-
-
' Loop through all tables in the database.
-
Set dbs = CurrentDb
-
For Each tdf In dbs.TableDefs
-
' If the table has a connect string, it's a linked table.
-
If Len(tdf.Connect) > 0 Then
-
tdf.Connect = "ODBC;Driver={SQL SERVER};" & "Server=DBSERVER\DB1;" & "Database=" & sDatabase & ";" & "Trusted_Connection=no;" & "Uid=sa;" & "Pwd=secret"
-
Err = 0
-
On Error Resume Next
-
sCurrentTDF = tdf.Name
-
tdf.RefreshLink ' Relink the table.
-
If Err <> 0 Then
-
RefreshLinks = False
-
Exit Function
-
End If
-
End If
-
Next tdf
-
-
RefreshLinks = True
-
ExitOut:
-
Exit Function
-
ErrorOut:
-
msgBox ("There was an error refreshing the link(s) for '" & sCurrentTDF & "': " & vbCrLf & vbCrLf & Err.Description)
-
Resume ExitOut
-
End Function
I run this from a Form that supplies the Database to relink to. It comes in handy during deployment to switch the Backend Database from a Development Database to a Production Database
Thank you jforbes. Do I need that pwd=secret statement? I dont use password on my db.
Once again, thank you.
AF
jforbes 1,107
Recognized Expert Top Contributor
I'm not 100% sure what you would do for a blank password, probably include the parameter but pass a blank string, I think like this: - tdf.Connect = "ODBC;Driver={SQL SERVER};" & "Server=DBSERVER\DB1;" & "Database=" & sDatabase & ";" & "Trusted_Connection=no;" & "Uid=sa;" & "Pwd="
For your reference, here are some other examples of a Connection Strings: https://www.connectionstrings.com/mi...r-odbc-driver/ zmbd 5,501
Recognized Expert Moderator Expert
aflores41
if NOT using SQL server then the code J gave you can be simplified.
jforbes 1,107
Recognized Expert Top Contributor
Ah, nice catch Z. I've got some tunnel vision going on. =)
Thanks. The code is not working for me though. I'm not linking to SQL Server just to the backend access data.
Any suggestions?
Thanks.
-AF
jforbes 1,107
Recognized Expert Top Contributor
I apologize for taking you down a path that you didn't need to travel. I primarily use Access as a Frontend and SQL as a Backend, so when you asked about linking, I forgot to keep in mind that there are multiple ways and reasons to link to a database.
You'll want to pretty much forget what I posted as it is skewed towards SQL.
I think this is what you are looking for: http://allenbrowne.com/ser-13.html I haven't used this code, but it looks like the right approach.
...Or the Article Seth has posted =)
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Bruce |
last post by:
Greetings!
After finally figuring out that the Linked Table manager is no longer
an add-in in Access 2003 I now discover that it does not display any
of my linked tables in the 'select linked tables to be updated' list.
Although I have about 30 linked tables in my application, absolutely
none of them are showing up in the list. I compacted...
|
by: Ian Davies |
last post by:
Does anyone know how to open the linked table manager using VBA code behind
a comand button in Access2000
Ian
|
by: Wayne |
last post by:
I've just performed a reformat and reinstalled Access 2000 and 2003 and
as usual the linked table manager in Access 2003 is broken ie. it
doesn't show any tables. I've gone through the process of
re-registering Accwiz.dll as outlined in the MS Knowledge Base and
although this process has worked for me before, it hasn't worked this
time. ...
|
by: steph |
last post by:
Hi,
I've got a problem. (Microsoft Access 2002 SP3)
One of our users found a problem in a when accessing a form with a
subform: when starting it access crashes leaving the message
"MSACCESS.EXE - Application Error ... The instruction at "..."
referenced memory at "...". The memory could not be read".
Now if I remove the subform from...
|
by: sj |
last post by:
I have written an application (CW.mde) with a seperate data (Data.mda)
file. My Data tables are linked to CW.
I want my user to be able to re-link the data.mda when they change
location/path of the data.mda. Hence, I have an button with the
following:
DoCmd.RunCommand acCmdLinkedTableManager
The button works on the computer with Ms...
| |
by: JodiPhillips |
last post by:
Hi,
My first post! I've basically taught myself Access and the little I know about VBA through reading these forums and a couple of books.
I'm in the middle of a project at work - to put it mildly I've been thrown in at the deep end, in the past I've just done simple databases and that's been fine. Now, however I have to "roll out" various...
|
by: nico5038 |
last post by:
Access 2007 Linkedtable manager refuses to relink tables having a field with the "Attachment" datatype.
Problem:
When placing a split database in another folder, the Linked table manager should be used to relink the tables. The Linkedtable manager does however refuse to relink tables having an "Attachment" datatype field.
Symptoms:
Open...
|
by: Davidsm |
last post by:
Hi Guys
Please can you assist me I have created a ODBC link to a sql server and linked tables from sql the problem is in the linked manager it does not show any tables linked. Under the tables tab the tables show but on field in not being populated with the Amount it leaves the column blank but in the sql quert the amount column is populated....
|
by: franc sutherland |
last post by:
Hello,
I have a client who uses Access 97, but does not have the Linked Table
Manager. The system is split into front and back end.
Is it possible to install the Linked Table Manager into Access 97?
If not, how does one manage the table links?
Thanks,
|
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...
|
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. ...
| |
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...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
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: 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...
| |