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?
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 am using access as a front end to SQL Server 2005 and link tables via ODBC.
Today when I run a query (normally works fine) I receive an ODBC Failed message box. I have tried opening each link table connected to the query and can open fine.
I have a new user who is trying to connect wirelessly. I set up the ODBC connection on his laptop but when he tries to run a report that uses queries looking at linked tables he gets ODBC call failed. Is it because he is trying it wirelessly. I'm just the data guy so I don't know too much about networking.
I have a standalone Access DB with 4 linking tables therein. They all point to tables that are stored in a server Access DB. I can open 3 of these tables in datasheet view without any problem, except one. Whenever I try to open it, I am prompted following msg "ODBC call failed etc... server API error (#669) etc..." And when I click OK to close the Error msg box, then another msg box appears with the msg: "Microsoft Access can't open the table in Datasheet view"
I've deleted the link table and recreated it. But I still can't open the table in datasheet view. Does anyone knows what's to do? :confused:
I am running Access as a front end to SQL and maintain all reports and forms from a .mdb. I then release an .mde to a different location for end users to use.
Each time I release an .mde and a user attempts to run a report an "ODBC call failed" error appears. So I have to re-link tables on users pc's. The Users ODBC connections are set up the same as mine.
I am facing a problem with ODBC call failed error latly.The problem doesnt occur on my development PC but only on other PC's.I'm a beginer in Access, vba and I cant find a solution to make it work on every machines.
Code:
Dim qSQL As String DATA = "#" & DatePart("m", DATA) & "/" & DatePart("d", DATA) & "/" & DatePart("yyyy", DATA) & "#" qSQL = "SELECT Sum([IL]*[TO])" & _ "FROM PUBLIC_S INNER JOIN PUBLIC_SD ON PUBLIC_S.U = PUBLIC_SD.U " & _ "WHERE (((PUBLIC_SD.DAT)=" & DAT & ") AND ((PUBLIC_SD.ANA)='" & ANA & "') AND ((PUBLIC_SD.IA)='" & ia & "') AND ((PUBLIC_SD.AN_C)='PIMSS')); Set Db = CurrentDb() Set rs1 = Db.OpenRecordset(qSQL)
I have an ODBC connection to our company's data that uses Macola. I can enter criteria in the fields in Access to filter that data, but when I try to put a parameter query in such as [Enter order number:] I always get an "ODBC-call failed" message, error# 3146. Can I even solve this problem, or am I just not going to be able to actively change the criteria unless through a pivot chart or table, or by hand? Can anyone help? Thanks.
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:
Hi! I have a query setup that feeds off of three combo boxes on a form. It is setup to allow a user to search and calculate revenue for a selected time period by company, then department, then page. If all three or just company are selected, there are no issues running the data. However, if only company and department is selected I receive an 'ODBC--Call Failed' message. The issues seems to be stemming from the department field as I can link the department table to the main table and the query will run without error. The problem with that bandaid is that the query takes more than 15 minutes to run that way as opposed to about 2 minutes to pull more data at the company level.
Any suggestions on why this is happening or a fix that can be put in place? Any help is greatly appreciated.
When running a query in Access 2013 or 2010 we get an ODBC call failed. However when we run just the form, which the query connects to, it works just fine.
I would like to use code to change the connection string for linked tables in my front end database. I have tried using the connection property of the TableDefs collection, this seems to work while I step through the code but does not change the existing connection property. Any suggestions?
I have built a form without a record source as i need to mess around with the fields before i save.
On save, i would like the form to save to an ODBC linked table in my database.
I save the record through a save button which has the following code attached to its on_Click event.
' NOTE: DAO 3.6 code Dim db As Database Dim rs As DAO.Recordset
Set db = CurrentDb Set rs = db.OpenRecordset("tblODBCLinked", dbOpenTable) With rs .AddNew .Fields("Country") = Me.txtCountry .Fields("ID") = Me.ID .Fields("Creation Date") = Now() .Update End With rs.Close Set rs = Nothing
I have no problem saving to a non-linked table with the above coding but always seems to fall over if as soon as i try to open the linked table. Ive debugged and the problem seems to be with opeing the table.
Set rs = db.OpenRecordset("tblODBCLinked", dbOpenTable)
Thanks in Advance for any suggestions
Access 97 / Windows NT / Full Read Write access. Not a permissions issue
In a local DB table or a data grid view, the columns have an arrow at the top next to the name. When the arrow is clicked, all the entries in that column are displayed with check boxes. They are listed underneath the "sort" and "text filter" options. I am working on a DB project with ODBC linked tables instead of a local access table.
My linked table does not have this same functionality. It is missing the names with check boxes where I can select individual entries. I don't know the correct terminology for this functionality I am describing. That makes searching tough. What this is called and why the tables would be different.
Although familiar with CR, SQL and other such things. I have never had cause to use Access all that much.... Until Now!
My problem is easiest explained by my requirements....
I have a db table called Source. The data for this Table is populated by a form called Technical. Technical containes a series of fields (SO No, SO Line No, WO No etc etc). Some of these fields are lookup fields linked directly to the SO_Header Table in our main ERP software via and ODBC connection. The result is the SO No field is populated with a list of Sales Order No's as they exist within the ERP software. Within the Technical Form I have also the Customer Name.
This is where the problems begin. Within the ERP software we have two tables which I need to use: SO_Header and Customer. The SO_Header contains all the Sales Order info and the Customer table contains all the customer info. What I need to do is populate the customer name field in by db with the relevant customer based on the SO No chossen. However the Customer Name does not exist in the SO_Header table. Instead both the SO_Header table and the Customer tables contain a field called Cust_Code.
Now if I wanted to link these in Crystal Reports I would link from the SO_Header table to the Customer table using the Cust_code field. This would give me the corresponding Customer name based on the Sales Order No.
How can I get the correct Customer Name to appear automatically in my Access form based on a SO No chossen by the user?
I would realy appreciate any help on this! If you need me to clarify anything please ask.
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.
I have a number of databases that use ODBC connections for working with linked tables from a SQL database. The source SQL data has been moved to a new server with new sa credentials. I have updated my local ODBC connections to use this new information. However, when I added numerous tables to the databases I checked the box to save the password so that it wouldn't prompt me or other users every time I wanted to use the Access database. Is there a way in Access to change this stored password? So far I haven't been able to find one.
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 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?
