Hi,
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
which way is rather faster?
My problem is best explained by example;
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 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
How to Update ODBC linked tables on ms access?
View 5 Replies View RelatedHi!
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?
If this question is overly trivial, I apologize.
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
Thanks in advance for your help :D
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 ?
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:
18598979 3 06-DEC-13 10:10
18598979 0 03-DEC-13 10:34
18598979 1 03-DEC-13 10:34
18598979 2 06-DEC-13 08:20
However when I link the table through access and view the same job number within the table (not even using a query) it displays the following:
18598979 3 06/12/2013 10:10:00
18598979 3 06/12/2013 10:10:00
18598979 3 06/12/2013 10:10:00
18598979 3 06/12/2013 10:10:00
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.
Hello,
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 :-(
Regards,
Matt.
Hi All,
I hope somebody can help me on this.
I still use Access 97.
I have 4 tables that contain a vehicle registration number field as their primary key and have one-to-one referential integrity applied.
I want to add a new registration to all 4 tables from one query. How?
I've tried many permutations with no success, such as putting the registration on a new table and trying to apply this to the 4 RI tables. I keep getting ref intergrity violations. Obviously I could disable the ref integrity rules, update the tables and then reapply the rules, but this is not possible if I am going to make the database available to a user group. A new registration needs to be added seamlessly. By not having ref integrity could leave the DB in an inconsistent state.
Help! If what I want is not directly possible, then any work-round would be appreciated.
Thanks in anticipation
Richard
Hi All,
I hope somebody can help me on this.
I still use Access 97.
I have 4 tables that contain a vehicle registration number field as their primary key and have one-to-one referential integrity applied.
I want to add a new registration to all 4 tables from one query. How?
I've tried many permutations with no success, such as putting the registration on a new table and trying to apply this to the 4 RI tables. I keep getting ref intergrity violations. Obviously I could disable the ref integrity rules, update the tables and then reapply the rules, but this is not possible if I am going to make the database available to a user group. A new registration needs to be added seamlessly. By not having ref integrity could leave the DB in an inconsistent state.
Help! If what I want is not directly possible, then any work-round would be appreciated.
Thanks in anticipation
Richard
I have two tables tbl1 and tbl2
tbl1 has 10 fields named tbl1.id tbl1.field2 tbl1.field3 tbl1.field4
tbl2 has only three fields tbl2.field1 tbl2.field2 tbl2.field3
Now i need to insert values into tbl1::
tbl1.field1 tbl1.field2 tbl1.field3
from
tbl2.field1 tbl2.field2 tbl2.field3
respectively, but i need to make sure if tbl2.field3 value is already there in tbl1.field3 then we don't need import those records. so we only need records if value of tbl2.field3 is not already there in tbl1.field3.
Please let me know What statement do I need to write so i can import all data from tbl2 into tbl1 by comparing as above.
Hi all,
I've got one question again.
Let's say. I've created several tables to store data for several categories.Note: there is no relationship between each table.In each table, there is the date field in which the record is created.
Then I want to make another daily record form that is to find records from all categories which is created at the current date.
Is this possible to search records from various tables and combine them to show in one form or report?
Can anyone help me in this case?
thanks in advance.
I'm new to Access2007. I created 5 tables all related to each other in series with one-to-many joins (type 2 and all enforcements on).So an Id in table 1 can show up several times in table 2, and the same is true from tables 2 to 3, 3 to 4 and 4 to 5.I want to enter new records in such way that I can see all tables at once. I tried to do that with a query, but I can only modify records, not add new ones.I have no problem adding records to individual tables but have not figured out how to do it to this All-tables query.
View 2 Replies View RelatedHi,
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 can edit/open existing linked tables (to Sage Line 50, v12) but not create new ones.
Following the procedure:
- New
- Link Table
- File Type: ODBC databases ()
Nothing now happens!
I can open the Link Table Manager to update previously created links.
Any ideas?
I'm using MSAccess 2003 on a brand new machine.
I Need help,
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?
Thanks
Frank
Hi All,
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.
Thanks in advance!
RobV
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.
View 1 Replies View RelatedI am at work, and I have acquired a database that prints labels. They now want the database to be coded so that after certain labels are printed the database will print a blank label. I have the code figured out as a Do While statement in order to print the blank label. The problem I am having is that I am trying to use the Insert Into command to insert the filepath into the table that adds the blank label.
|DoCmd.RunSQL "INSERT INTO Rod_tmakLabels ( Print, [Order] ) SELECT Yes AS Expr1, 'Rods Labels' AS Expr2"|
If I run the above command, it just adds the text "Rods Labels" at the end of the table. Is there anyway with the INSERT INTO command that I can insert the new label between the 2nd and 3rd row and add another row? Or is the command designed only to add a new row to the end? I haven't had any luck searching for this yet.
I am currently using Microsoft Access 2010 32bit, and have one database acting as parent, with a second linked database as a client for people to work with, and the parent database has tables imported from Sage V21 via ODBC. I have used the following code as specified in other examples as follows:
Code:
DoCmd.TransferDatabase acImport, "ODBC Database", "ODBC;DSN=MyDNSMachineName;UID=MyUID;PWD=MyPassword;LANGUAGE=us_english;" & "DATABASE=pubs", acTable, "INVOICE", "INVOICE"
one of the tables has 10k records in it, it only transfers 77 records. After manually attempting an import via the ODBC wizard it finally lets me access all 10k records.I wish to have a single button click delete and import fresh tables without worrying if all the data is coming across.Also, when manually adding a table, I am usually asked by the wizard to specify an index, but with the code above, I am not offered that option and the tables come across with no index. I am led to believe that having tables that link to each other without an index is bad, so how do I ensure an index is created?
I have a database with a number of linked tables that are linked to tables in different databases (not a back-end).for example, I have table1 that is linked to table1 in K:databasedb1.mdb.table2 linked to table2 in S:datadata.mdb.and so on...
However, recently we have moved all our databases to a new location.
K:databasedb1.mdb is now residing in O:masterdatabase
and S:datadata.mdb is now residing in O:masterdata
and so on...
I'm now in charge of relinking all those tables to point to the new location.I would do this in linked table manager one by one but we have 100s of tables linked to multiple different databases in different location.is there a way to create a VBA code that will automatically do this re-linking process?
so,
1. find unlinkable tables
2. search its new location under O:master
3. re-link it to the new location
Database names and tables names have not been changed. Just the location of databases.
I have three tables which I have linked with relationships. The linked fields are given the ID number of the parent, where I wanted the value. What on earth am I doing wrong, something stupidly obvious I bet! Access 2007
View 3 Replies View RelatedHello, this is my first post (anywhere ever)!:eek:
While I have lots of Access programming experience over the years I have broken new ground this week and am in unchartered territory and need help please!
In the organization I am working for (as Procurement Manger), we use AMMS (by Microwest) for work order generation and inventory management, etc. I have been able to import into Access the tables of interest from this SQL-based package. The problem is that I need to share the tables (via nice front end forms/queries) with about 20-30 people in the field.
I don't know much about ODBC but the administrator of AMMS was kind enough to give me access to the AMMS database tables via ODBC and I, personally have access to read the table data but none of the other people in the field can access these tables due to security rights.
So, what I have done is I created MS Access tables for each of the 4 AMMS tables of interest and I run code that runs delete and append queries on each table. I then have a separate public db that the field people access with all the queries and forms but only links to the Master db containing the 4 Access tables derived from the ODBC tables. This works ok but there are issues:
1) if any field people are on the Public db, the code to import the ODBC table data loops and does not complete the delete/append cycle on one or more of the tables. I can only refresh the table data when nobody is connected to the db - impractical.:(
2) the ODBC tables are rather large - one is over 38,000 records and it takes several minutes to run the delete/append queries.:mad:
3) When I created the Master db it was some 83MB. After running the delete/append queries it is some 179MB and appears to increase in size every time I run the delete/append code. When I run "compact db" it stops running after a short time. Does nothing but create a new file (db1.mdb) of the same huge size as the Master.:confused:
I suspect the most graceful solution would be to give each user rights to the ODBC tables from AMMS in Access. In this case, I wouldn't have to run delete/append queries and the data would always be in relative real time subject to the refresh timer. Unfortunately, due to AMMS licensing restrictions, this is not doable.
I have tried to search the internet for guidance but no luck. This forum appears to be an excellent opportunity to get expert advice on this particular issue. I thank you for your thoughts and guidance.
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.
I wonder if anyone had any suggestions regarding how to link 4 ODBC tables so the info in each of the 4 ODBC tables are in one table. The 4 ODBC tables are linked in from a mapping system. Each table has 80% the same fields with some minor differences. i dont know if a union query would be the best answer in this case as the field in each of the ODBC's tables do not exactly match. Some have 12 fields, some have 14 fields. Any help would be appreciated?
also this would be using Access 2003 if you would like to know. thanks