Having another brain fart on what I believe is a no brainer.
I'm trying to read the contents of a DBF and write it to an ACCDB table; this is a daily event and the DBF has between 500-1200 records each day.
I have successfully read the contents of a DBF table and filled my da.Fill(ds, "Data") with it along with a DataGridView.
Now I want to write the entire table with NO parameters to my ACCDB Table 'BlahBlah'; both structures are the same. I see a ton of samples & examples WITH parameters, but no cmd.command INSERT examples to insert/copy everything.
Basically I want to: INSERT INTO [BLAHBLAH] SELECT * FROM ds.tables("data") ....something like that
Everything related to the connection string, data source, etc is fine; I've been using it for other procedures with no problems.
What I can't seem to get is: - the INSERT statement - whether I loop through the datatable row by row, or can copy thr whole thing in one blast
I don't think you can do this with INSERT INTO [BLAHBLAH] SELECT * FROM ds.tables("data") because you are using two connections. You can only do that type of insert when the databases are on the same server. You are not working with a large number of records so I'd just brute force record by record using ADO. Because you are not getting user input there should be no risk of SQL injection so parameterized queries aren't necessary. What you end up with looks pretty much like
Dim con1 As New ADODB.Connection
Dim con2 As New ADODB.Connection
Dim rec As New ADODB.RecordSet
con1.Open(source db connection string)
con2.Open(destination db connection string)
rec.Open(selection query, con1, CursorTypeEnum.adOpenStatic)
Do Until rec.EOF
Dim query As String = "INSERT INTO..."
Just add the field values into query by rec("fieldname").Value
I already have all the records in a dataset/datatable and have closed the connection to the DBF file. Now I just want to write the datatable to my Access DB. The datatable has about 65 fields (exact same as Access table) so I'm trying to avoid identifying all these fields in the query. I just want to INSERT everything, whether it be by brute force or a bulk copy.
I just don't know how to express that in the cmd.commandtext query.
Ah. So if I have the sequence of events correct you want to
open the connection to the source database
populate the datatable from the source table
close the connection
connect the datatable to the destination database
insert from the datatable to the destination table
It makes sense but I am not well versed in datatables (I tend to avoid middlemen objects whenever possible) so I can't really offer a suggestion. If I think of one before someone else posts a solution I'll post it here.
The data in the datatable does NOT have any unique IDs relative to any database structure, that will come later as they are introduced to the Access table with a AutoNumber field. But there is but there is a 'uniqueness' to each line of data. It is impossible that any 2 rows could/would be identical.
OK.....I'll accept this reality and try joining the modern world. Can someone tell me the difference between using a data adapter vs a cmd.commandtext to do my SELECT, INSERT, DELETE operations?
Is there any particular reasons, advantages, logic to use of one over the other? I notice that in previous projects I've used both and I'm not really sure why other than I 'found similar code and I could easily adapt it'.
Really want to learn here and I appreciate your opinions. At my age I don't have time to go back to school :)
Even I was wondering about this... But when I googled i got an information like this...
Updating a data source is much easier using DataAdapters. It's easier to make changes since you just have to modify the DataSet and call Update.
This is probably no (or very little) difference in the performance between using DataAdapters and Commands. DataAdapters internally use Connection and Command objects and execute the Commands to perform the actions (such as Fill and Update) that you tell them to do, so it's pretty much the same as using only Command objects.