asked on
How do I transfer one MS Access table into another MS Access Database using ADO/VB.NET
Hello, here is my code; I have sucessfully made the two connection strings, dataAdapters etc...I also made a dataset which fills into a list box which gives me a visual confirmation that I am receiving data into my dataset from my first database connection string.
The problem is I can't figure out for the life of me how to transfer that data into the other database. I see alot of code out there but none of which I see is viable; i.e. it seems to be "Work arounds".
I got a majority of this figured out; I think; I just need to get the data from one database and put it into another Microsoft Access Database.
Thank you for what ever help any of you can offer.
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim conStr As String = "Provider=Microsoft.JET.OL
Dim strDest As String = "Provider=Microsoft.JET.OL
Dim sqlStr As String = "SELECT * FROM site"
' Create connection object
Dim conn As OleDbConnection = New OleDbConnection(conStr)
Dim conn2 As OleDbConnection = New OleDbConnection(strDest)
' Create data adapter object
Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)
Dim da2 As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn2)
' Create a dataset object and fill with data using data adapter's Fill method
Dim ds As DataSet = New DataSet
da.Fill(ds, "site")
' Attach dataset's DefaultView to the datagrid control
Dim dv As DataView = ds.Tables("site").DefaultV
ListBox1.DataSource = dv
ListBox1.DisplayMember = "sitename"
End Sub
The problem is I can't figure out for the life of me how to transfer that data into the other database. I see alot of code out there but none of which I see is viable; i.e. it seems to be "Work arounds".
I got a majority of this figured out; I think; I just need to get the data from one database and put it into another Microsoft Access Database.
Thank you for what ever help any of you can offer.
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Dim conStr As String = "Provider=Microsoft.JET.OL
Dim strDest As String = "Provider=Microsoft.JET.OL
Dim sqlStr As String = "SELECT * FROM site"
' Create connection object
Dim conn As OleDbConnection = New OleDbConnection(conStr)
Dim conn2 As OleDbConnection = New OleDbConnection(strDest)
' Create data adapter object
Dim da As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn)
Dim da2 As OleDbDataAdapter = New OleDbDataAdapter(sqlStr, conn2)
' Create a dataset object and fill with data using data adapter's Fill method
Dim ds As DataSet = New DataSet
da.Fill(ds, "site")
' Attach dataset's DefaultView to the datagrid control
Dim dv As DataView = ds.Tables("site").DefaultV
ListBox1.DataSource = dv
ListBox1.DisplayMember = "sitename"
End Sub
.NET ProgrammingVisual Basic.NET
Last Comment
DataMix
do you want to copy the whole database or just a table to a database?
ASKER
Hello, and thank you for responding to my request. I want to really copy two tables however if I can figure out how to copy one then I can do the other. So for now I want to copy one table from database A and put that table into database B.
More information here. I have 54 databases with hundreds of tables and only two I need; I want consolidating them into one database so I can easily move it into SQL or simply just do reports in Access. I figure I should be looking at SQL statements like 'Append' or 'Insert' the tables into a master table.
I have not been able to organize in my mind the method but I am figuring that I would have to write a query for the INSERT transaction or some more complex code which I am not that good yet to be able to do. I do have quite a bit of VB experience though.
Is this enough information for you or more confusing?
More information here. I have 54 databases with hundreds of tables and only two I need; I want consolidating them into one database so I can easily move it into SQL or simply just do reports in Access. I figure I should be looking at SQL statements like 'Append' or 'Insert' the tables into a master table.
I have not been able to organize in my mind the method but I am figuring that I would have to write a query for the INSERT transaction or some more complex code which I am not that good yet to be able to do. I do have quite a bit of VB experience though.
Is this enough information for you or more confusing?
here is some code that you can look at.
you may need more. just let me know.
you may need more. just let me know.
#Region "Make a Table to a Database"
Public Sub FillTable(ByVal sNewTable As String, ByVal sOldTable As String, ByVal sDBName As String)
sSql = "INSERT INTO " & sNewTable & " IN " & sDBName & " SELECT FieldName1, FieldName2, FieldName3, FieldName4, FieldName5 " & _
"FROM " & sOldTable & " IN " & sDBName
RunSQL(sSql)
End Sub
Private Sub RunSQL(ByVal strSQL As String)
Dim Cmd1 = New OleDb.OleDbCommand(strSQL, Con)
Try
Cmd1.Connection.Open()
Cmd1.ExecuteNonQuery()
Cmd1.Connection.Close()
Catch exp As OleDb.OleDbException
Dim errorMessages As String
Dim i As Integer
For i = 0 To exp.Errors.Count - 1
errorMessages += "Index #" & i.ToString() & ControlChars.Cr _
& "Message: " & exp.Errors(i).Message & ControlChars.Cr _
& "NativeError: " & exp.Errors(i).NativeError & ControlChars.Cr _
& "Source: " & exp.Errors(i).Source & ControlChars.Cr _
& "SQLState: " & exp.Errors(i).SQLState & ControlChars.Cr
Next i
Dim log As System.Diagnostics.EventLog = New System.Diagnostics.EventLog
log.Source = "My Application"
log.WriteEntry(errorMessages)
MessageBox.Show("An exception occurred. Please contact your system administrator.")
Catch exp As Exception
Cmd1.Connection.Close()
'' MessageBox.Show(exp.Message, MsgBoxStyle.Critical, "General Error")
End Try
End Sub
#End Region
#Region "Copy a Table"
'USE CopyTable("D:\athos.mdb", "client", "clienttest")
Public Sub CopyTable(ByVal DBPathName As String, ByVal TargetTable As String, ByVal NewTableName As String)
Dim cmd1 As New OleDb.OleDbCommand("SELECT " & TargetTable & ".* INTO " & NewTableName & " IN '" & DBPathName & "' From " & TargetTable)
cmd1.Connection = Con
Con.Open()
Try
cmd1.ExecuteNonQuery()
Catch ex As Exception
MessageBox.Show(ex.Message)
End Try
End Sub
#End Region
ASKER
Hello, thank you for you help; I do see and understand your example and I am working with it; now I also viewed the link you sent me and this is exactly what I am tyring to avoid; making a statement for every field. If I have to do that I will; but I can't beleive a perosn can just do this in two queries in ADO while using a DataSet.
However, so we are on the same page; the example you gave here does not inculde a dataset; instead it is a query based solution making use of command objects. Correct?
However, so we are on the same page; the example you gave here does not inculde a dataset; instead it is a query based solution making use of command objects. Correct?
Yes, I have not looked into the dataset side. but I do agree that you would think a easy dataset fill and copy to a new table would be easy. i haven't tried it , but we will see.
ASKER
Hello, thank you again for your response. I would agree it should be easy to just dump a dataset into a table i.e. 'Append' or 'Insert' (the same thing). But what is stupid on the .net end is they make it easy one way but not going the other. I think you would agree that I have getting the data out of the database although I could have put the query in a CommandText but filling the table and displaying it is the easy part.
Now do I have list out every single field/column and insert? I don't think you have to do that; I think their is a much easier way. I can't believe more people have not done this before. In fact when I search the internet I am finding I am not the only person to have this problem; in fact their is almost no 'EASY' solution for Appending data from a dataset from one database to another database. I am for the first time stumped.
Now do I have list out every single field/column and insert? I don't think you have to do that; I think their is a much easier way. I can't believe more people have not done this before. In fact when I search the internet I am finding I am not the only person to have this problem; in fact their is almost no 'EASY' solution for Appending data from a dataset from one database to another database. I am for the first time stumped.
I found this code for you to look at...
Private Sub AccessToAccess(ByVal sourceFile As String, ByVal sourceTable As String, ByVal targetFile As String, ByVal targetTable As String)
'Sub transfers all records from ..mdb sourcefile.sourcetable ...
'... to .mdb targetfile.targettable
'It is assumed that the .mdb targettable definition already ...
'... exists, with the same number and types of fields, ...
'... in the same order, as the .mdb sourcetable fields.
'It does not depend on the field names being the same ...
'... in the source and target tables.
Dim sourceConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sourceFile & ";User Id=admin;Password=;"
Dim sourceSQL As String = "SELECT * FROM " & sourceTable
Dim targetConStr As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & targetFile & ";User Id=admin;Password=;"
Dim targetSQL As String = "SELECT * FROM " & targetTable
'use dataadapter for target and command builder to ...
'... create insert command, including parameter collection
Dim targetCon As New OleDbConnection(targetConStr)
Dim targetDA As New OleDbDataAdapter(targetSQL, targetCon)
Dim cb As New OleDbCommandBuilder(targetDA)
Dim targetCommand As OleDbCommand = cb.GetInsertCommand
'now do the work
Dim sourceCon As New OleDbConnection(sourceConStr)
Dim sourceCommand As New OleDbCommand(sourceSQL, sourceCon)
targetCommand.Connection.Open()
sourceCon.Open()
Dim sourceReader As OleDbDataReader
sourceReader = sourceCommand.ExecuteReader()
While sourceReader.Read()
'for each row from source
For i As Integer = 0 To sourceReader.FieldCount - 1
'load values into parameters
targetCommand.Parameters(i).Value = sourceReader(i)
'if any of the source data needs to be modified ...
'... for the target, it could be done here ...
Next
'... or here
'then write to target
targetCommand.ExecuteNonQuery()
End While
sourceReader.Close()
sourceCon.Close()
targetCommand.Connection.Close()
End Sub
OK I findly found the code that we where looking for that uses datasets thanks you EE RonaldBiemans:
Check it over and test your data to see if it will work.
Check it over and test your data to see if it will work.
'Ron used 2 access databases db1, db2 as you can see in the connection
' strings with identical tables called tabel1 in db1 and tabel2 in db2,
' Ron added to oledbdataadapters (da1,da2)
Dim con1 As String = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Jet OLEDB:Database Password=;Data Source=""C:\" & _
"db1.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet" & _
" OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:S" & _
"ystem database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Je" & _
"t OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:" & _
"Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=Fals" & _
"e;User ID=Admin;Jet OLEDB:Encrypt Database=False"
Dim con2 As String = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
"ocking Mode=1;Jet OLEDB:Database Password=;Data Source=""C:\" & _
"db2.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet" & _
" OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:S" & _
"ystem database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Je" & _
"t OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:" & _
"Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=Fals" & _
"e;User ID=Admin;Jet OLEDB:Encrypt Database=False"
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim ds As New DataSet
Dim da1 As New OleDb.OleDbDataAdapter("select * from tabel1", con1)
Dim da2 As New OleDb.OleDbDataAdapter("select * from tabel2", con2)
da1.Fill(ds)
Dim db As New OleDb.OleDbCommandBuilder(da2)
da2.Update(ds.Tables(0))
End Sub
ASKER
Hello, O.K here is what I tried with my input informaiton; From what I see this should work and I don't know what I am missing here but it does not. However, notice I put in the listBox2 which verifies that I am grabbing data from the connection string 1 (con1).
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Setting the connection string to the type and path(s) of the databases
Dim con1 As String = "Provider=Microsoft.JET.OL
Dim con2 As String = "Provider=Microsoft.JET.OL
'Setting up a new dataset
Dim ds As New DataSet
'Create dataAdapters
Dim da1 As New OleDb.OleDbDataAdapter("se
Dim da2 As New OleDb.OleDbDataAdapter("se
'Fill the dataset with the results of the queries form the dataAdapters getting t
'the informaiton from the tables
da1.Fill(ds, "site")
'This is the command creates a new instance of db using the command builder and (da2)
Dim db As New OleDb.OleDbCommandBuilder(
'This I beleive should tell the da2 dataAdapter to update the table in the second connection string
'with the dataset created from the first
da2.Update(ds.Tables(0))
'This is just another list box to help me make sure the infomraiton is
'being pulled form the connection string
Dim dv As DataView = ds.Tables("site").DefaultV
ListBox2.DataSource = dv
ListBox2.DisplayMember = "sitename"
End Sub
End Class
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'Setting the connection string to the type and path(s) of the databases
Dim con1 As String = "Provider=Microsoft.JET.OL
Dim con2 As String = "Provider=Microsoft.JET.OL
'Setting up a new dataset
Dim ds As New DataSet
'Create dataAdapters
Dim da1 As New OleDb.OleDbDataAdapter("se
Dim da2 As New OleDb.OleDbDataAdapter("se
'Fill the dataset with the results of the queries form the dataAdapters getting t
'the informaiton from the tables
da1.Fill(ds, "site")
'This is the command creates a new instance of db using the command builder and (da2)
Dim db As New OleDb.OleDbCommandBuilder(
'This I beleive should tell the da2 dataAdapter to update the table in the second connection string
'with the dataset created from the first
da2.Update(ds.Tables(0))
'This is just another list box to help me make sure the infomraiton is
'being pulled form the connection string
Dim dv As DataView = ds.Tables("site").DefaultV
ListBox2.DataSource = dv
ListBox2.DisplayMember = "sitename"
End Sub
End Class
Ok, I found the problem in that the insert command would error.
So I added this new code and now the moving in the tables works.
Also I added some code in the conection strings so you will need to copy in all of the snippet.
So I added this new code and now the moving in the tables works.
Also I added some code in the conection strings so you will need to copy in all of the snippet.
Dim con1 As String = _
"Jet OLEDB:Global Partial Bulk Ops=2;" & _
"Jet OLEDB:Registry Path=;" & _
"Jet OLEDB:Database Locking Mode=1;" & _
"Jet OLEDB:Database Password=;" & _
"Data Source=C:\temp.mdb;" & _
"Password=;" & _
"Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB:Global Partial Bulk Ops=2;" & _
"Jet OLEDB:Global Bulk Transactions=1;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:System database=;" & _
"Jet OLEDB:SFP=False;" & _
"Extended Properties=;" & _
"Locale Identifier=1033;" & _
"Mode=Share Deny None;" & _
"Jet OLEDB:New Database Password=;" & _
"Jet OLEDB:Create System Database=False;" & _
"Jet OLEDB:Don't Copy Locale on Compact=False;" & _
"Jet OLEDB:Compact Without Replica Repair=False;" & _
"User ID=Admin;" & _
"Jet OLEDB:Encrypt Database=False"
Dim con2 As String = _
"Jet OLEDB:Global Partial Bulk Ops=2;" & _
"Jet OLEDB:Registry Path=;" & _
"Jet OLEDB:Database Locking Mode=1;" & _
"Jet OLEDB:Database Password=;" & _
"Data Source=C:\temp2.mdb;" & _
"Password=;" & _
"Jet OLEDB:Engine Type=5;" & _
"Jet OLEDB:Global Partial Bulk Ops=2;" & _
"Jet OLEDB:Global Bulk Transactions=1;" & _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Jet OLEDB:System database=;" & _
"Jet OLEDB:SFP=False;" & _
"Extended Properties=;" & _
"Locale Identifier=1033;" & _
"Mode=Share Deny None;" & _
"Jet OLEDB:New Database Password=;" & _
"Jet OLEDB:Create System Database=False;" & _
"Jet OLEDB:Don't Copy Locale on Compact=False;" & _
"Jet OLEDB:Compact Without Replica Repair=False;" & _
"User ID=Admin;" & _
"Jet OLEDB:Encrypt Database=False"
Private Sub Move_datsets()
Dim ds As New DataSet
Dim ds2 As New DataSet
Dim da1 As New OleDb.OleDbDataAdapter("select * from table1", con1)
Dim da2 As New OleDb.OleDbDataAdapter("select * from table1", con2)
da1.AcceptChangesDuringFill = False
da1.Fill(ds)
ds2.Merge(ds)
Dim db As New OleDb.OleDbCommandBuilder(da2)
db.QuotePrefix = "["
db.QuoteSuffix = "]"
da2.Update(ds2.Tables(0))
End Sub
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
Hello, Thank you again for this code when I execute this for now I am getting an error stating that I have duplicate values in my index. So I am checking my table(s) now and reviewing that. I might even put a list box up to see what kind of data is comming up.
ASKER
I would like to thank you for your help; I have now gotten this project underway and the data transfer is working beautiful. I will now add my condition structure and the rest of the code and I am a happy camper.