Hi!
Does anyone know how I can copy an Access table from a database that is included in my project to another database located in the C:\Temp folder for example using VB 6.0?
Any help would be appreciated.
Thanks,
B
- Follow via:
- RSS
- Email Alert
Question
0
Votes
Answers (3)
0
Votes
Have you...
Added a refernece in your project to the Access ## Object Library? Check the MSDN docs for the object library and I am sure it will have info to do what you want to do. Writing code takes a while and doing the research will help you understand and learn it better.
28th Nov 2007
Replies
Below is the code that I used. I have all the variable predefined so I did not include them anymore. in the code below. It stores the data table into the dataset but it does not update the dataset. Anyway, it's okay I guess I could do it another way.
Thanks for your reply.
Dim table As DataTable
Dim copyTable As DataTable
Dim arrTable(25) As Object
Try
For Each item In checkedItems
strList = strList + item.SubItems(0).Text + vbCr
arrList(z) = item.SubItems(0).Text
z = z + 1
Next
myConnectionSource = New OleDb.OleDbConnection(strConnectionSource)
myConnectionSource.Open()
For Each table In CoaSfaDataSet2.Tables
If table.TableName.ToString.Contains("SFA") And _
table.TableName.ToString.Length 15 Then
If arrList(x) = table.TableName Then
arrTable(x) = table.Copy()
'copyTable = table.Copy()
CoasfaDataSet.Tables.Add(arrTable(x))
'myDataSet.Tables.Add(copyTable)
x = x + 1
z = z - 1 : If z = 0 Then Exit For
End If
End If
Next table
CoasfaDataSet.AcceptChanges()
myConnectionSource.Close()
myConnectionSource = Nothing
Catch
MsgBox("Cannot proceed! {0}." + e.ToString(), MsgBoxStyle.OkOnly, "NOT SUCCESSFUL")
End Try
Thanks for your reply.
Dim table As DataTable
Dim copyTable As DataTable
Dim arrTable(25) As Object
Try
For Each item In checkedItems
strList = strList + item.SubItems(0).Text + vbCr
arrList(z) = item.SubItems(0).Text
z = z + 1
Next
myConnectionSource = New OleDb.OleDbConnection(strConnectionSource)
myConnectionSource.Open()
For Each table In CoaSfaDataSet2.Tables
If table.TableName.ToString.Contains("SFA") And _
table.TableName.ToString.Length 15 Then
If arrList(x) = table.TableName Then
arrTable(x) = table.Copy()
'copyTable = table.Copy()
CoasfaDataSet.Tables.Add(arrTable(x))
'myDataSet.Tables.Add(copyTable)
x = x + 1
z = z - 1 : If z = 0 Then Exit For
End If
End If
Next table
CoasfaDataSet.AcceptChanges()
myConnectionSource.Close()
myConnectionSource = Nothing
Catch
MsgBox("Cannot proceed! {0}." + e.ToString(), MsgBoxStyle.OkOnly, "NOT SUCCESSFUL")
End Try
bdelacruz246
30th Nov 2007
0
Votes
Really Just SQL Statement
The trick is using a properly formatted SQL statement. Make a connection to the source database, then execute the query:
SELECT * INTO [tablename] IN [sfile] FROM [tablename]
Where [tablename] is the name of the table being copied and [sfile] is the destination filename.
SELECT * INTO [tablename] IN [sfile] FROM [tablename]
Where [tablename] is the name of the table being copied and [sfile] is the destination filename.
30th Nov 2007
Replies
I will take your suggestion into account. Right now I wanted to try to just copy the table from one database into another without using SQL, but using objects instead.
bdelacruz246
30th Nov 2007
0
Votes
copy access table
rivate Sub CopyTable(strFromDatabase As String, strTableName As String, strToDatabase As String)
On Error GoTo errCopyTable
Dim strSQL As String
Dim dbConFromDatabase As ADODB.Connection
'open connection from database
Set dbConFromDatabase = New ADODB.Connection
dbConFromDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strToDatabase & ";Persist Security Info=False"
dbConFromDatabase.Open
strSQL = "SELECT * INTO " & strTableName & " FROM " & strTableName & " IN '" & strFromDatabase & "';"
dbConFromDatabase.Execute strSQL
dbConFromDatabase.Close
Set dbConFromDatabase = Nothing
Exit Sub
errCopyTable:
MsgBox "Error in Sub CopyTable:" & Err.Description, vbOKOnly
End Sub
On Error GoTo errCopyTable
Dim strSQL As String
Dim dbConFromDatabase As ADODB.Connection
'open connection from database
Set dbConFromDatabase = New ADODB.Connection
dbConFromDatabase.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strToDatabase & ";Persist Security Info=False"
dbConFromDatabase.Open
strSQL = "SELECT * INTO " & strTableName & " FROM " & strTableName & " IN '" & strFromDatabase & "';"
dbConFromDatabase.Execute strSQL
dbConFromDatabase.Close
Set dbConFromDatabase = Nothing
Exit Sub
errCopyTable:
MsgBox "Error in Sub CopyTable:" & Err.Description, vbOKOnly
End Sub
30th Nov 2007
Replies
I am using Visual Studio 2005 and it only uses OLEDB to connect to an Access database. I could not find the ADODB namespace. Thanks for your help though.
bdelacruz246
30th Nov 2007
