Question

  • Creator
    Topic
  • #2223668

    Copy an Access Table from one Database to Another Using VB

    Locked

    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

All Answers

  • Author
    Replies
    • #2632085

      Clarifications

      by bdelacruz246 ·

      In reply to Copy an Access Table from one Database to Another Using VB

      Clarifications

    • #2632074

      Have you…

      by scott_heath ·

      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.

      • #2479800

        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

    • #2480618

      Really Just SQL Statement

      by texasjetter ·

      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.

      • #2479799

        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.

    • #2479791

      copy access table

      by trappt ·

      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.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

      • #2479720

        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.

Viewing 3 reply threads