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.
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
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?
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.
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 need to update the names of my ODBC linked tables in my Access database, how can I do this without causing issues with my queries/reports?The current linked tables are to a SQL View on a database called mcsrm_live, and called e.g. vwDamagesReportNew
The new SQL views that I need to link to are identical in structure and content and on the same SQL server but different database - forkdw and are called e.g vw_R_Damages
Is there a straightforward process to do this without affecting the queries and reports in my Access db?
I'm trying to change the table links to a password protected BE DB. I found an example online, which I adapted to my needs. When I set it up to fail to find the normal BE it seems to work as intended until it gets to the line "Tdf.RefreshLink". Then it crashes with a 3031 "Not a valid password" error. The code is:
Code: Private Sub Form_Open(Cancel As Integer) Dim Dbs As Database Dim Tdf As TableDef Dim Tdfs As TableDefs Dim NewPathname As String
[Code] ....
I can't find any references or example to relinking a password protected table.
I am after a script to change the linked table paths like the following.
It will only ever be the path that is changing not the file name and only ever linked tables
Code: for each table in tabledefs if table.path = c:Testing* then table.path = w:Testingfilename if table.path = c:Jobs* then table.path = w:Jobsfilename if table.path = c:Quotes* then table.path = w:QuotesNewfilename next table
I have a access 2007 file. with a table with a yes/no field. When I link the table to another db using lnked table manager, the yes/no display is not a checkbox (as set) but is turns into textbox.
Sorry if this is an easy one and I just missing it, but I have a form in an Access2003 database with option groups for ratings for 18 individual tasks ie: Safety: 1. Satisfactory 2. Marginal 3. Unsatisfactory 4. N/A The value stored in the table is 1,2,3, or 4 for each one, obviously. How can I change the stored value in the table to "Sat" "Marginal" "Unsat" etc? Or, if easier, make the report convert the numerical values to names?
I am having difficulty with my Access database, when I run a particular query. The query is linked to a table in SQL Server, but I keep getting a "ODBC -Call Failed" error message. After some research I understand I might need to change the ODBC Data Source, but this is something I have never done and am unsure of how to do it so my query will work!
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
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 ?
Does anyone know how to change all linked tables in a database to unlinked? I want to save a database for offline testing and want to save the tables as flat tables instead of linked. Is there anyway to do this without bringing them all back in unlinked?
I have a problem with a single query I have build in Access 2000. Basically, I have a normalised database structure to 3NF. I have a table called Data, a table called Reports and a table called DataReports which cotains the primary keys from both data and reports which are named Data_ID and Report_Id. Basically, what is happening is I am building a query that returns a record from DataReports using the Data_Id field as criteria as this is held on a form. I create a one to many relationship from the Reports table to the DataReports table using a link from one Report_Id to the other. I save the table and it works fine...once only!! The query is used to feed a list box on a form. The form loads correctly but when I go back into design view to make a change to something else on the form and then go back to form view, I get Data Type Mismatch error. When I look at the query, Access has changed the link from the Report_Id --> Report_Id to Report_Id --> Report_Title which is the next field down in the Reports table. Because all ID's in my database are Autonum's it is trying to link a number to text so I understand why this would not work but I dont understand why on earth access is changing my links. It is very frustrating. Could anyone help me please.
I am trying to build a Form that will show an estimate (then eventually will be moved to a project if customer and employee aggree to price and project) in a Form F_Estimates is a M_Customers(Customer_ID) (Based on a Table) and thier info in a Subform. Also is the "projected costs" from parts out of the Parts(Part_ID) (Based on another Table) in a second Subform as a list that I need to calculate $$$ in (Dang that still sounds evil and definately NOT understandable even after edit... so)
Here's some basic info
Tables
EstimatesandParts - Table EstimatesandParts_ID : Autonumber Estimate_ID : Number Part_ID : Number
Parts - Table Part_ID : Autonumber PartNumber : Text (not a number due to some part#s have letters in them) PartName : Text Unit Price : Currency Description : Text
Estimates - Table Estimate_ID : Autonumber InvoiceNumber : Text (again can have letters in it) EstimateDate : Date/Time EstimateTime : Date/Time Employee_ID : Number Customer_ID : Number ProblemDescription : Memo
Customers - Table Customer_ID : Autonumber FirstName : Text LastName : Text CompanyName : Text Address : Text City : Text Province_State : Text Postal_ZIPCode : Text (CDN Postal codes are letter num letter...)
you can see the link table in the EstimatesandParts Table
Now I want to use that link to populate a subform in the F_Estimates form
Forms
SF_Customers - SubForm
(all boxes atm are text boxes on this form till I figure out the Parts section then will use same base for this so I can pick any customer in the database to be the customer for this estimate. Also will have ctrl button for making new customer with customer form and a refresh on Focus Gain bit of code)
FirstName LastName CompanyName Address City Province_State Postal_ZIPCode
SF_Parts - SubForm Default View -Continuous Forms
(want it to be a list of parts that I can grab prices and descriptions from then in a bit of code to calculate a cost of parts)
Part_ID : Combo Box Control Source - Part_ID Row Source Type - Table/Query Row Source - SELECT Parts.Part_ID, Parts.PartNumber, Parts.PartName, Parts.UnitPrice, Parts.Description FROM Parts ORDER BY Parts.Description;
(Pulls info from the table Parts for input into a list of parts to be used on that project)
PartName : Text Box UnitPrice : Text Box
(here's where I run into problems due to the fact that the form is not based on the parts table but rather the link table EstimatesandParts so I can't propogate the info to the 2 other text boxes, ps I dont care if they cant be text boxes and have to be linked or some other type I'm not "set" just need to find out how to make it work )
(have tried a couple things to complete this task)
(works AWSOME ... for ONE ROW then propogates the second selection to the first and second and third selection to first second and third and so on ...)
(tried to make control source for the txtPartName to)
=Forms!Parts!Partname
(Doesnt exist .. akkk, cant use ActiveForm either as it doesn't focus on the SubForm but the MainForm ... cry)
(Combo Boxes Select Customer and Employee from list of present ones of each)
SF_Customers SF_Parts
(Both SubForms on the main form)
Now this is an Exerp from my entire Database I like to work on one small problem at a time and I have made this its own little database till I figure out the problem then I will bring the info I learn back into the rest of the database and go from there ...
Hope you can help I have a feeling I will need to make a recordset and go from there but I'm just not able to wrap my head around that for some reason
Thanks in advance for ANY and ALL help that I get from here
This is one of this only times i've had a problem whose answer i couldn't find on this or other forums, so here's the question.
My DBase links to the back end tables tblBrkdn tblBrkdnArchive tblBrkdwnTradespeople tblBrkdwnTradespeopleArchive
The following two tables are local, not linked tblBrkdnArchiveTemp tblBrkdwnTradespeopleArchiveTemp
After running through the following code, all of the four above mentioned linked tables are now local. It's vexing. I'm terribly vexed. If you can help, my status would chnge from vexed to joyous....:
Thx in advance...
' 1) Join all together (Each Table) ' Create two recordsets, One for the Brkdn set, one for the Tradespeople Set. ' Brkdn: Want it to be the Union of all records in (tblBrkdn, tblBrkdnArchive) without duplicating BrkdwnID 'Standard Union Query with no duplicates. Transferring data into a temporary table SQLStr = "SELECT * INTO tblBrkdnArchiveTemp FROM (" SQLStr = SQLStr & "SELECT tblBrkdn.* FROM tblBrkdn INNER JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "Union ALL " SQLStr = SQLStr & "SELECT tblBrkdn.* " SQLStr = SQLStr & "FROM tblBrkdn LEFT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "WHERE (((tblBrkdnArchive.BrkdwnID) Is Null))" SQLStr = SQLStr & "UNION ALL SELECT tblBrkdnArchive.* " SQLStr = SQLStr & "FROM tblBrkdn RIGHT JOIN tblBrkdnArchive ON tblBrkdn.BrkdwnID = tblBrkdnArchive.BrkdwnID " SQLStr = SQLStr & "WHERE tblBrkdn.BrkdwnID is null);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' BrkdnTradespeople: Want it to be the Union of all records in (tblBrkdwnTradespeople, tblBrkdwnTradespeopleArchive) without duplicating ID 'Standard Union Query with no duplicates. Transferring data into a temporary table SQLStr = "SELECT * INTO tblBrkdwnTradespeopleArchiveTemp FROM (" SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* FROM tblBrkdwnTradespeople INNER JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "Union ALL " SQLStr = SQLStr & "SELECT tblBrkdwnTradespeople.* " SQLStr = SQLStr & "FROM tblBrkdwnTradespeople LEFT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "WHERE (((tblBrkdwnTradespeopleArchive.ID) Is Null))" SQLStr = SQLStr & "UNION ALL SELECT tblBrkdwnTradespeopleArchive.* " SQLStr = SQLStr & "FROM tblBrkdwnTradespeople RIGHT JOIN tblBrkdwnTradespeopleArchive ON tblBrkdwnTradespeople.ID = tblBrkdwnTradespeopleArchive.ID " SQLStr = SQLStr & "WHERE tblBrkdwnTradespeople.ID is null);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'delete everything from the four non-temporary tables 'tblBrkdn SQLStr = "DELETE * FROM tblBrkdn" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 2) Paste the entire record set to each Brkdn table 'tblBrkdn SQLStr = "SELECT * INTO tblBrkdn FROM tblBrkdnArchiveTemp" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'tblBrkdnArchive SQLStr = "SELECT * INTO tblBrkdnArchive FROM tblBrkdnArchiveTemp" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 3) Delete from Active & Archive Brkdn tables WHERE [DATE] < txtArchiveDate.Text 'tblBrkdn SQLStr = "DELETE * FROM tblBrkdn WHERE ([DATE]<#" & dateStr & "#);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
'tblBrkdnArchive SQLStr = "DELETE * FROM tblBrkdnArchive WHERE ([DATE]>=#" & dateStr & "#);" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 4) Paste to Active Tradespeople table WHERE Associated Breakdown Record [DATE] >= txtArchiveBeforeDate.text SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeople FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdn ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd n.BrkdwnID)" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
' 5) Paste to Archive Tradespeople table WHERE Associated Breakdown Record [DATE] < txtArchiveBeforeDate.text SQLStr = "SELECT tblBrkdwnTradespeopleArchiveTemp.* INTO tblBrkdwnTradespeopleArchive FROM tblBrkdwnTradespeopleArchiveTemp INNER JOIN tblBrkdnArchive ON (tblBrkdwnTradespeopleArchiveTemp.BrkdwnID=tblBrkd nArchive.BrkdwnID)" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True ' 6) Clean up: Delete all records from the temporary tables 'tblBrkdnArchiveTemp SQLStr = "DELETE * FROM tblBrkdnArchiveTemp;" DoCmd.SetWarnings False DoCmd.RunSQL SQLStr DoCmd.SetWarnings True
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'm making a database for HR. I have an entire form of all the fields from one table. But I made another table for credentials because one employee could have more than one.
Now, the form: Sample.jpg
I want to display the credentials fields on one of the tabs. I want it to look like a datasheet (datasheet view). But I don't know how to link it to the current employee ID. I've already set the relationships for the t_Employee and t_EmployeeCredential. I've already tried making a subform. But it just doesn't automatically reflect. I'm not sure if I'm making sense. I can't really explain it.
