Software

Ask something
Question Locked

Copy an Access Table from one Database to Another Using VB

0 helpful
7 replies

by bdelacruz246

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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

7 replies

by scott_heath

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.

by bdelacruz246

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

by texasjetter

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.

by bdelacruz246

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.

by trappt

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

by bdelacruz246

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.

This discussion is closed to new replies.