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 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 an access 2002 front end and I am linked to a sql server express 2005 backend and in one of my tables I am getting this error when I add a record to a work order table. The error is ODBC connection on INSERT failed on linked table.
A few strange rhings happen. It only involves customers with over (approx.) 140 work orders (there is a one to many with customer to work order). All other customers seem to be immune to this.
Also, if you go to the tables directly from a access database window, you can still add/edit or delete records, but in the form view, it gives you an error.
I have tried several things including changing code (the orgional code was set dbs= currentDB and dbs.execute("INSERT.......)
I have found similiar posting but no real direct causes. does anyone have any ideas on how to resolve this?
I have split a few different MS Access databases before with the BE on a shared drive and the FE installed on all users' local machine. In this instance, I have three ODBC connections bringing in data to my tool from an outside database once a day. Is it faster to store the odbc connections on the FE or BE when I split them?
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 Access front end connected to a MS SQL database using a ODBC connection which is working fine of course.
When I compile it as a runtime version and install it on the business computer I get an error with the ODBC connection (I can't remember exactly what pops up). On the business computer I have installed SQL Native Client and in the ODBC added a System DSN (which works).
I am in need of consultation for MS Access reading data from ODBC connection. I have SQL Server that has all the data for the project financials etc.
I need a database that will read only certain data from the tables, for example, I don't need to import all 500,000 lines from SQL through ODBC connection, I just want to bring certain data for a list of projects whichever are opened and load only that data in MS Access so the group then can add additional details for that project in a shared MS Access.
Right now, all I can do is connect to that database through ODBC and brings all the data which I don't need all as it increases the size of the database, but just a criteria to specify which data to bring, if that's possible to do.
I am working on an MSACCESS database that reads data from SQL Server through ODBC Connection.
I have multiple tables that I have linked in and created Queries that read the data and created Make Table query.
However, I am asking if there's a way to bring the data through a query that doesn't need the linked tables. Is there a way to bring different fields from different tables into one query without needing to import all the tables, since the tables have many fields that I don't need.
For example in MS Excel, I am able to do that, a query that will bring external data from SQL Server from different tables and only brings the fields that I need for my report.
In Access Database A, I have some linked ODBC tables (to an SQL Server database) and some local tables.
In Access Database B, I have a front end which links to all the tables in Access Database A.
The problem is, when I try to link the tables in B, I cannot see the ODBC tables linked in A. Is there any reason why not? 'Why not link Access Database B to the SQL Server tables directly?', you may ask. Well, there is an answer to that but it is quite difficult to explain. I assure you there is one. :p
I'll cut right down to it: I have an ODBC connection to a (SQL Server) test database on my development machine that is identical to the live database to which a (different) ODBC connection exists on the PC where this Access application is to be deployed. The problem is that when I deploy, there are some tables that have to be updates, because they still point to my test database. The other tables look like they use the default database for that user.
I used to be able to just copy the Access application and it would work without having to update any linked tables.
To clarify, the tables that work without a problem look like this in the Linked Table Manager:
t_mytable (DSN=odbc;)
And the problem tables have this:
t_mytable2 (DSN=odbc;DATABASE=my_test_db)
I don't seem to be able to clear that DATABASE= thing up. Can anybody tell me how this works?
I have a lot data to append to ODBC linked table in MS Access. I want to know that which way is faster to append the records.
if I append the data into ODBC linked table, 1) create the one query (append) to insert the records into ODBC linked table 2) use the VBA code (DAO/ADO) to insert the records into ODBC linked table
My tables are sybase linked tables to my Access front end. I get this error whenever i click away from my mainform while my not-null fields (textboxes) have not been entered. So, i would like to put an error message "somewhere" that tells the user to fill in the necessary fields and highlight the necessary textboxes in red, instead of this scary ODBC message http://img82.imageshack.us/img82/6829/error0el.png :eek: :eek:
When i put my error handling in the afterUpdate or afterInsert Event. I still get the above error msg instead of my msgbox. Where do i put this message? or should be question be, "what the proper way to handle this intimidating error? :)
Here's my error handling:
On Error GoTo Err_Form_AfterInsert
'do something
Exit_Form_AfterInsert: Exit Sub
Err_Form_AfterInsert: MsgBox Err.Description, vbExclamation, "Please enter the Project Name, Project Leader Initials, Main User and Status Code" & Err.Number Me!Combo67.BackColor = "red" Me!Combo65.BackColor = "red" Me![Project Name].BackColor = "red" Me![Project Leader Initials Combo].BackColor = "red" Resume Exit_Form_AfterInsert
Using access 2007 (and tried 2003) I have a linked ODBC table which is using an ORACLE driver. If I query the table directly through ORACLE/SQL for a particular 'job number' I get the following results:
As you can see it only displays the MAX date/time and associated data from the 4 available records, duplicating the record. I've checked the field formats and they all look fine. It does the same if I pull through the table on it's own or with other linked tables.
The scenario. Two PC's, one older than the other, both running Windows 2000 (SP4) and Office 2000 (SP3). I use Access as a front end to a MySQL database, connections are made using ODBC. I recently altered the structure of a table and attempted to re-link the table in access using the Linked Table Manager on the new PC. All that happened was the hourglass came on and never went away. I tried to do this on the old PC and it worked fine first time.
Any ideas why I can't get it to work on my new PC? It's quite important as I won't have the old PC with me much longer :-(
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 am creating an Access database that will be used by multiple users. This database will be on the network and will be the front end of an oracle database connected to it via ODBC. Do I have to set up the ODBC to oracle only on the machine that hold the database on the network or do I have to set up an ODBC on each user's machine?
Hi i would like to make a question about ODBC i have created an access database that connects via ODBC to an SQL server database, with the help of someone else i have created this code that works well for the link of 1 table that i choose to link the problem is that i want to link all tables from database and not doing it 1 by 1.
Is there a way to link all tables from database ???
The code i'm using is :
Dim strConnect As String Dim strDatabase As String Dim strTableName As String
'Checks to see if table exists. If it does, delete it. If TableExists(strTableName) Then DoCmd.DeleteObject acTable, strTableName
'And then use the trusty old TransferDatabase method to re-link the table declared in strTableName from the SQL Server DoCmd.TransferDatabase acLink, "ODBC Database", strConnect, acTable, strTableName, "dbo_Tbl_A_Yles"
I want a linked table in my current database. The server where the other access database is already has a ODBC connection created. How to I create a linked table to that other Database through the odbc? I know this is very unspecific, but I do not know what to do on either end. Thanks
I have been happily linking to a MySQL database via ODBC for a while but all of a sudden, Access 2003 is crashing whenever I try to see the actual MySQL database. No message given. It just gives the Microsoft Error window and closes down the MDB. ODBC tests succesfull. Any ideas?
I am an A2K novice and I need some help with connecting to an AS/400.
I have my ODBC data source created and it works well. I'm comfortable with the SQL syntax I'll need in A2K to fetch my AS/400 data. I have no idea what is necessary in the way of VB code in A2K to do the connection. Can somebody point me in the right direction where I can find some code samples? I searched this board prior to submitting this thread but came up empty. Time is ticking on this project.
Hi, I have a front end that i have developed in access which manipulates a table stored in an oracle database. I have an ODBC connection to the oracle table.
When i open the access front end i am confronted with the
Oracle ODBC Driver Connect and am promted for username, service name and password.
My question is... Can I in any way take what is inputted into the driver connect for username and password. For example to deactivate/activate some command buttons depending on the username that is inputted
Can anything like this be done or is this driver connect a totally individual thing?
We use an Oracle base software called Trapeze and Microsoft Access sometime used as a front hand to access the Trapeze oracle tables. Due to the possible dangers of allowing the users Access, Is there a way to block the users access to Administrative Tools, to create ODBC connectivity to our databases? Trapeze's security is antiquated and uses Oracle to validate accessibility. Since a user has to be created in Oracle, with update, delete, read, write, append rights, for Trapeze to work correctly, a user can connect directly to the Db through Access and make changes directly to the data, unless the ability to prevent them from creating their own ODBC connections.". Any ideas on how to prevent them to create that ODBC connection?Thank for your help.
I have a Acces 2000 front end that links to an Oracle 9i back end. Everytime i open a form/table the ODBC dialog box appears asking for username/password/server. Is there a way to bypass this using vba code so that when the Access app starts the connection is established behind the scenes and therefore preventing the dialog box appearing.
