Modules & VBA :: Refresh Linked Tables When Connection Lost Without Closing Access Database
Sep 4, 2014
I have MDB database linked to SQL SERVER through VPN connection.I created links to the sql server Links are dsnless..Everything works fine but when I lost VPN connection or sql connection has been broken I can't refresh links to the tables.I receive message 3146 sql connection failed..I must close database and start again...
I tried different methods like ado,dao, and vba docmd.transferdatabase,aclink... but no success, table cant be relinked.
Only way I can relink is to change ip adress in conn.string
E.g. 192.124.0.2 (1st ip- router server ip) and after connection failed i can use 192.124.0.32 (2nd ip - server local ip) and that's it if i lost connection for the 3rd time... i must restart application.
It seems that access database keep the previous connection..how to reset or drop database connection to the sql server and refresh links to the tables with vba code without closing access database...
I have linked tables from SQL Server using ODBC connection that their location never changes. I have used certain fields of those tables to create queries and make table queries to derive to the information I needed.
On these tables on SQL Server, there is new data added daily. Every day, midnight, there's new data records added of whatever transactions took place in that working day. how often do I need to refresh linked tables in this case to get the latest data added. I mean, once I am linked, the make table query using those defined fields, would it get the latest data added by default when the query is executed, or I must refresh linked tables using Linked Table Manager and then run make table query.
Also, if I want the access to automatically refresh linked tables, can I use the following code? I have added this code, and executing it through a button, but I don't see anything happen, the database becomes inactive for couple seconds (I guess while it is updating) but I don't know is it updating the tables for sure or not, though I am not receiving any error when executing the code through the button.
Function RefreshLinkedTables() Dim tdf As TableDef For Each tdf In CurrentDb.TableDefs If Len(tdf.Connect) > 0 Then tdf.RefreshLink End If Next td End Function
I have a back end 2002 DB. I would like to disallow access to this through odbc connection. Is there some way this can be achieved? Thie directory where it is located is unfortnately cannot be restricted as the front end mde also resides there.
I have a question that I have a Microsoft Access database (.accdb) front-end/backend split and I want to give the database to my company client. As we have the different path for the backend/frontend linked. I want some code that will popup if the database location is not found and popup with the dialog so the user then select the backend and it would be ready and there is no need to popup each time the database open, it would run once it did not find the last linked path.
Also I have tried the code of Dev Ashish URL.... but unfortunately it would ask everytime to refresh the table links and I only want to run the process of linking tables when the database start and the linked path not found.
Our master database has linked tables with ODBC to our SQL database and linked tables to another access database. On the computer i'm running this from a System DSN data source has been created to connect to our SQL database.
The problem I'm having is when I run Macros to kick off Queries, there seems to be some kind of timeout issue and its not consistant. Sometimes in Access after a hour or two, the ODBC links somehow just fails. I have to go up to the menu and go to 'Get External Data' and then 'Link Tables' and relink the ODBC DSN connection. I make sure to hit save password and the end but that doesn't seem to do much. This also happens sometimes when I close and reopen the database.
But it seems erratic, sometimes it can stay up for hours but sometimes just a few mins. Sometimes I can close the Access database and reopen without losing connection. Being so erratic, this makes it very hard for me to run batch files nightly to kick off Macros. Is there some kind of inactivty timer for DSNs? Or maybe with the SQL database itself? Or is the way i'm restablishing the ODBC connection wrong ?
I read a lot about Access database bloat when using temporary tables that reside in the front end, which was where I created and used temporary tables before reading about bloat.I therefore wrote a routine on startup that creates a temporary database and then copies the structure of the temporary tables into that temporary database using the DoCmd.TransferDatabase command. It kills the DB on exiting the application.I also read about keeping a persistent connection (handle) open to a linked DB as long as the main application is active, and this for efficiency reasons. I got this to work as well.
Being a self-taught Access programmer I have two questions:
1.Is the use of docmd.Transferdatabase a good way to work and does it reduce bloat by transferring the structure of the temp files to the temporary database? 2.If I want to work with the tables in my temporary database I use the following code for example: Dim dbTemp As Database temporary database Set dbTemp = DBEngine.Workspaces(0).OpenDatabase(strDBTemp) Set rstRpt = dbTemp.OpenRecordset("tblRptPU001", dbOpenDynaset) .. My question is this: if, at the end of the procedure, I write dbTemp.close set dbTemp = Nothing
does this leave the persistent connection open? The reason for the question is that I intend to split the database and soon go multi user, which is when efficiency is even more important than when working on a temporary database.
The Header and footer are not linked. The Footer simply displays all the existing bookings for said ouse/Room/Date combination. (Date being all dates between the StartDate and EndDate) BUT (surprise, surprise)users don't look at this to check if a booking already exists.
Also - It seems pointless to have the users enter the same data (House, Room, UserID) in 6 times (one for each day that they want to book the room.
So I am trying to automate the process.
Therefore, I append each 'new' record (that the users adds in this session) into a temporary table. (House / Room / UserID / StartDate)....note, no end date because I need a separate record for each day.
I have a query that is supposed to check the EXISTING bookings with the temp table. My code (paraphrased here for simplicty) says
do while StartDate < EndDate - if Qry_CheckForClash returns 0 records then '(uses a dlookup) - append from TEMP to BOOKINGS. - - else - compile an alert message to the user (appending the Room No and Date each iteration) - In Qry_CheckForClash, increase the StartDate by +1 - end if loop
My problem is that after the last step (increase StartDate by +1) the call to Qry_CheckForClash still returns the initial StartDate. But when I check the TEMP table, the StartDate has changed.
So how do I REFRESH or REQUERY a query that is not associated to a form or report?
these are two issues that are making some problems to me.in a few words i will make a description.there is a html that contains data.this data is updateable.i have made a link to my excel.i need to add somethings before they come to access.i have made a link in an access getting all data in it.i have made a update query to store data in another table so i will not lose previous data.all excel and access work opened.so at start it works fine.after 1-2 hours or maybe more a alert display and eather there is lost connection or it says that another user is using the data or about permissions.i then have to restart my pc so it can work again.all is working on windows xpsp2 and office 2003. please send reply if anyone knows something.thank you.
I recall reading somewhere (fairly recently) about a parameter or property in Access which is "On" by default and allows a form/query to identify when backend table field names or query arguments are changed and automatically looks for and "fixes" the renamed link. By leaving this switched ON, the database is slower than it could be. The db speed performance can be can be improved significantly by switching this 'feature' off. (i.e. Name of the property)
I have a Access FE linked to a Access BE on a computer. Whenever the user of that computer shuts off, the Access FE handles this in worst way possible: It spews a zillion dialog warning of 'disk or network not found' before my error handler even takes over.
Has anybody come up with a way to catch such situation?
I think the damage to my database is not recoverable, but i'm still hoping. My computer crashed when I was working on my dtabase and after restart the db is not working anymore :( If I try to do something, anything (!!!) all I got is an error message: "Error accessing file. Network connection may have been lost." But the db is on my computer, I don't use it over the network. What can I do to make it working again? Thanx!
We have an Access 2000 backend database resides in a network server drive while users connect to the backend tables with a mde file on their computers. All users have Access 2010 and the mde file was converted from the 2000 frontend with Access 2010.
The database have been running for a few weeks but recently users have been complaining about record lost on the backend table.We have also experienced one incident of data corruption where the main table could not be opened. After Compact & Repair, the table could be opened but a few records were showing xxxxx on all the fields and we have to delete and re-enter these records.Would they relate to using 2010 mde converted from 2000 frontend when the backend is still in 2000? I am a little nervous about converting both the frontend and backend to 2010 since I have heard various issues on the new version.
We have created a MSI file that installs some software onto a server (Windows 2003), including some (Access 2003) MDE files. These MDE files have linked tables to SQL Database (SQL Server 2000).
Each time we are testing the MSI install, we find we have to go into each MDE file and refresh the links within the "Linked Table Manager". The DSN's and SQL Database havent changed so does anyone know why we have to refresh each time? Or is it a known problem?
What we'd like to do is to install the the MSI and find the MDE's all work fine!!
I am looking to automate the process of linking my Access DB with an ODBC connection to an SQL DB with VBA (unless there's an easier way to do it?) - some sample code - if this is possible at all?
hi guys, this is my first thread on this forum...,, i am new out here..
i am not able to establish a connection with a MS Access database. I am using DSN Connection for it..
The error that i am getting is...//
Error Type: Microsoft OLE DB Provider for ODBC Drivers (0x80004005) [Microsoft][ODBC Microsoft Access Driver] The Microsoft Jet database engine cannot open the file '(unknown)'. It is already opened exclusively by another user, or you need permission to view its data. /airtickets/scripts/dbconn.asp, line 4
server administrators have expressed concern about my website that it is having a negative impact on the server. He said this typically occurs when a website has a memory leak (where it obtains server resources and doesn't release them) or is opening database connections and not closing them until the point where no more are available.
I'm using asp.net to control Access and open the connections. I don't know why it isn't closing them? the website is:
pedalcar.com
Any thoughts of what the problem is and what the statement I need to close the connections?
I have a database that runs a macro/report every 2 hours, however our network has been up and down lately. This causes the macro to fail when trying to log into the system because the table that is storing your username is no longer in the database. When I check, all tables, linked tables, forms, reports, macros and modules are all gone. I find the only way to get them back is to close the database and reopen.
What I want is a way to reconnect the objects without closing and reopening.
I have several dbf files that I imported into Access in order to establish relationships and export as spreadsheets. I have created a macro that does that. However, the dbf files are based on a query that changes monthly. I was wondering if there was any way to automatically update the tables from the dbf files once the queries are run without manually importing the tables again?
I have been developing Access 2K applications. Lately, every time I click a command button on a form or even open the code window for a form, I got this repetitive error message. I even create BLANK Access db and re-import all objects or create brand new form and copy the control designs/code from the error one to the new one. The new db or the new form still have the same ERROR, which is as follow :
"The expression On Click you entered as the event property setting produced the following error: Error accessing file. Network connection may have been lost. *The expression may not result in the name of a macro, the name of a user-defined function, or [Event Procedure]. *There may have been an error evaluation the function, event, or macro."
Please help! Thanks so much for your suggestions/ Have a great weekend,
I do not have a split db. I am prompted to enter my password when the db opens which is fine. However, when I close the db it prompts me to enter a password (not normal) and when I enter it it says invalid password. Also, when I try to change my password it says invalid password and when I try to compact and repair my db it say invalid password.
I want to delete an excel file from within access vba. Problem is that if the file is open, it obviously cannot be deleted. so I need to check to see if that specific excel workbook is open and if it is, I want to close it before I can delete it.
All of the answers here involve creating an excel object and opening it before closing it. If I do that all that happens is a second instance of the workbook opens then closes leaving the originally open workbook still open.
Sub xx() Dim XLapp As New Excel.Application Dim ObjXL As Excel.Workbook Set ObjXL = XLapp.Workbooks.Open("C:dropboxexcelimport.xlsx ") ObjXL.Application.Visible = True
[Code] .....
The above code demonstrates my problem, if the import.xlsx file is already open then the code just opens another instance of this workbook and then closes it again leaving the original workbook open and as such cannot be deleted.
I notice when you hover the mouse over a linked table in the Database window, that a tool tip appears displaying the connection string for that table. Where is this connection string stored and how may it be recovered by other means ( VBA )?
I have a inserted a linked table to my Access2007 DB. I want to manage disconnecting from this table, with the use of a button on a form, Then, with the use of another button, connect again. :eek: Is it possible? How???:confused:
