Question

Locked

Copy an Access Table from one Database to Another Using VB

By bdelacruz246 ·
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

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Answers

Collapse -

Have you...

by scott_heath In reply to Copy an Access Table from ...

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.

Collapse -

Thanks. I have a sample code.

by bdelacruz246 In reply to Have you...

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

Collapse -

Really Just SQL Statement

by TexasJetter In reply to Copy an Access Table from ...

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.

Collapse -

Thanks.

by bdelacruz246 In reply to Really Just SQL Statement

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.

Collapse -

copy access table

by trappt In reply to Copy an Access Table from ...

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

Collapse -

Thanks

by bdelacruz246 In reply to copy access table

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.

Back to Software Forum
7 total posts (Page 1 of 1)  

Software Forums