Overwrite insipte of appending ?? why ??
-
Dear ALl, I want to append an execel sheet to certain table in my ACCESS 2007 databas (VBA), through this: Dim exceltable1 As TableDef Dim strsql As String Set classdb = CurrentDb() Set exceltable1 = classdb.CreateTableDef("Temp") exceltable1.Connect = "Excel 8.0;DATABASE=Path\template.xlsx" exceltable1.SourceTableName = "template_table" '************ name definition of my ecxel records classdb.TableDefs.Append exceltable1 Set rst = classdb.OpenRecordset("select * from Temp") If Not (rst.BOF And rst.EOF) Then strsql = "insert into tb_contract_details select * from Temp" classdb.Execute strsql The problem is: If am running this code twice for same records of the excel sheet say (10 records) then the records will be the same 10 records in the database table not 20 records , ie: it is overwriting records. but if i change some values for some records in the excel sheet, it only inserts those changed values , while the rest are not inserted. Any help, i need to insert the records of the excel sheet even if they already exist in the table ??? i need to append them to the existing table records?? Keep in mind that no primary key is set in the table, no restriction on records.
0 will always beats the 1.
-
Dear ALl, I want to append an execel sheet to certain table in my ACCESS 2007 databas (VBA), through this: Dim exceltable1 As TableDef Dim strsql As String Set classdb = CurrentDb() Set exceltable1 = classdb.CreateTableDef("Temp") exceltable1.Connect = "Excel 8.0;DATABASE=Path\template.xlsx" exceltable1.SourceTableName = "template_table" '************ name definition of my ecxel records classdb.TableDefs.Append exceltable1 Set rst = classdb.OpenRecordset("select * from Temp") If Not (rst.BOF And rst.EOF) Then strsql = "insert into tb_contract_details select * from Temp" classdb.Execute strsql The problem is: If am running this code twice for same records of the excel sheet say (10 records) then the records will be the same 10 records in the database table not 20 records , ie: it is overwriting records. but if i change some values for some records in the excel sheet, it only inserts those changed values , while the rest are not inserted. Any help, i need to insert the records of the excel sheet even if they already exist in the table ??? i need to append them to the existing table records?? Keep in mind that no primary key is set in the table, no restriction on records.
0 will always beats the 1.
-
You have two data sources. You cannot create SQL that references both data sources at the same time. You must extract the data from one data source and then, in a different step, add it to the other data source.
Thanks Man , but i didnt got it at all....
jschell wrote:
You have two data sources.
What are those two???
jschell wrote:
You must extract the data from one data source and then, in a different step, add it to the other data source.
Can you be more clear, what should i do ?? detailed plz ?? Am sorry i couldnt get it :( :sigh:
0 will always beats the 1.
-
Thanks Man , but i didnt got it at all....
jschell wrote:
You have two data sources.
What are those two???
jschell wrote:
You must extract the data from one data source and then, in a different step, add it to the other data source.
Can you be more clear, what should i do ?? detailed plz ?? Am sorry i couldnt get it :( :sigh:
0 will always beats the 1.
scorp_scorp wrote:
What are those two???
You are getting the data from one place - that is data source one. You are putting the data some place - that is data source two.
scorp_scorp wrote:
Can you be more clear, what should i do ?? detailed plz ??
1. Get data - put it in a collection of some sort. 2. Write the data - by using the collection.
-
scorp_scorp wrote:
What are those two???
You are getting the data from one place - that is data source one. You are putting the data some place - that is data source two.
scorp_scorp wrote:
Can you be more clear, what should i do ?? detailed plz ??
1. Get data - put it in a collection of some sort. 2. Write the data - by using the collection.
Thanks for your follow man, but seems that we r not on the same channel.. :confused:
jschell wrote:
You are putting the data some place - that is data source two.
Putting data in some place , could be considered as Data Source ??? i dont know :doh:
jschell wrote:
1. Get data - put it in a collection of some sort. 2. Write the data - by using the collection.
U i tried to use 2 recordsets also didnt work, u mean that they should be done in two events ?? Any article?? any explanation ?? any examples ?? if you can provide .... most thanksfull :)
0 will always beats the 1.