Question
-
CreatorTopic
-
November 28, 2007 at 12:47 pm #2223668
Copy an Access Table from one Database to Another Using VB
Lockedby bdelacruz246 · about 16 years, 4 months ago
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,
BTopic is locked -
CreatorTopic
All Answers
-
AuthorReplies
-
-
November 28, 2007 at 12:47 pm #2632085
Clarifications
by bdelacruz246 · about 16 years, 4 months ago
In reply to Copy an Access Table from one Database to Another Using VB
Clarifications
-
November 28, 2007 at 1:17 pm #2632074
Have you…
by scott_heath · about 16 years, 4 months ago
In reply to Copy an Access Table from one Database to Another Using VB
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.
-
November 30, 2007 at 8:02 am #2479800
Thanks. I have a sample code.
by bdelacruz246 · about 16 years, 4 months ago
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 ObjectTry
For Each item In checkedItems
strList = strList + item.SubItems(0).Text + vbCr
arrList(z) = item.SubItems(0).Text
z = z + 1
NextmyConnectionSource = 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
-
-
November 30, 2007 at 4:32 am #2480618
Really Just SQL Statement
by texasjetter · about 16 years, 4 months ago
In reply to Copy an Access Table from one Database to Another Using VB
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.-
November 30, 2007 at 8:04 am #2479799
Thanks.
by bdelacruz246 · about 16 years, 4 months ago
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.
-
-
November 30, 2007 at 8:14 am #2479791
copy access table
by trappt · about 16 years, 4 months ago
In reply to Copy an Access Table from one Database to Another Using VB
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.OpenstrSQL = “SELECT * INTO ” & strTableName & ” FROM ” & strTableName & ” IN ‘” & strFromDatabase & “‘;”
dbConFromDatabase.Execute strSQL
dbConFromDatabase.CloseSet dbConFromDatabase = Nothing
Exit Sub
errCopyTable:
MsgBox “Error in Sub CopyTable:” & Err.Description, vbOKOnly
End Sub-
November 30, 2007 at 11:01 am #2479720
Thanks
by bdelacruz246 · about 16 years, 4 months ago
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.
-
-
-
AuthorReplies