|
|||||||||
|
|||||||||
| |||||||||
|
|||||||||
|
|||||||||
| |||||||||
|
|
|
| |||||||||
 |
|
|
«
Previous Thread
|
Next Thread
»
|
Thread Tools | Search this Thread | Rate Thread | Display Modes |
Dev Shed Forums Sponsor:
|
|
|
|
#1
November 18th, 2003, 05:54 PM
|
|||
|
|||
|
appending records to table
Hello guys, I have an access DBMS and in that database there are two tables, one of them is the main table and the other one is an imported table with new records. These new records need to append into the main table. So how can i append the new records to the main table and let the records that didn't append for some reason into the main table, show up in another table?
__________________
|
|
#2
November 18th, 2003, 10:24 PM
|
|||
|
|||
|
I figure out that u can append a part of the records into the main table use slq statement,and query the new records in imported table and the main table to find some record which don't exist in the main table by using sql's select statement..After find the records ,u can input these into the another table..
|
|
#3
November 18th, 2003, 11:33 PM
|
||||
|
||||
|
XY - if you're using ADO and SQL Statements, then the only way that you will know if there is a problem with the database transaction is by using the ADOError object. It is used by using
Code:
cnYourActiveConnection.Errors 'or cnYourActiveConnection.Errors.Count In SQL Syntax, you can use the Outer Join clause to join two tables where you want inequalities. In other words, you can join Table1 with Table2 on all records in table2 that are not in table1
__________________
Fisherman "Any intelligent fool can make things bigger, more complex, and more violent. It takes a touch of genius -- and a lot of courage -- to move in the opposite direction." - A.Einstein
|
|
#4
November 19th, 2003, 08:17 AM
|
|||
|
|||
|
appending records to table
thank you (cleverpig and fisherman)
Ok so far i managed to append the records to the main table by docmd.setwarnings false docmd.runsql "INSERT INTO main_table select * from imported_table" docmd.setwarnings true but this doesn't append all the records, there are a few with referential errors, records that contains values that does not match values in other related tables. With MINUS i can get the records that did not make it to the main table, so docmd.runsql "select * from imported_table minus select * form main_table and insert into Not_Imported_records_table" This doesn't work, can anybody tell me why?
|
|
#5
November 19th, 2003, 08:31 AM
|
||||
|
||||
|
OH....I've run into this problem once before, where I had an application that was loading new records into an existing customer table. If the customer was already there, and I was just adding another order for that customer, then I didn't add a new instance of the customer in my main table. If it was a new customer, then I had to add both the customer and their order to the appropriate tables. I did the following...
1. Determine whether it was a new customer or not. I declared a function with a boolean return value, and an input string = my customer name from my main table (global recordset). I declared a new ADODB connection and recordset and did the following... Code:
rsTemp = cnTemp.Execute "Select * from tblWhatever where Cust_Name LIKE '" & strMyCustomersName & "'" if rstemp.recordcount >0 then blnReturnValue=True Else blnReturnValue=False End If 2. Once I have my return value (True for new customer, false for existing) then I wrote a similar function to determine whether I had a new order, or was simply appending new items to an existing order. 3. If I get a value of true from the functions, then I append my data, otherwise, I move on to other checks to determine what I need to do.
|
|
| Viewing: Dev Shed Forums > Programming Languages - More > Visual Basic Programming > appending records to table |
| Thread Tools | Search this Thread |
| Display Modes | Rate This Thread |
|
|
 |
|
|
Del.icio.us
Digg
Google
Spurl
Blink
Furl
Simpy
Y! MyWeb
Linear Mode
