SQL command using access database

Posted on 2011-04-19
Last Modified: 2012-05-11
I need a SQL command to copy from table tab1 in A.mdb  to table tab2 on B.mdb. I´m using Visual Basic 2008
Question by:Jose Bredariol
    LVL 16

    Expert Comment

    IN A.mdb use

    DoCmd.CopyObject "directory path\B.mdb", "tab2", acTable,"tab1"

    Author Comment

    by:Jose Bredariol
    I´m using adodb. Like this :

    Rs1 = New ADODB.Recordset
    Xsql = "INSERT INTO ......."
    Rs1 = getRecordset(Xsql)
    LVL 16

    Accepted Solution

    Where are you running this from tab1 or tab2
    LVL 17

    Assisted Solution

    by:Carlos Villegas
    VB.NET? try:
            Using cn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Database1.mdb;")
                Dim cm As New System.Data.OleDb.OleDbCommand(Nothing, cn)
                ' This will clone the table [C:\Temp\Database1.mdb].Table1 to [C:\Temp\Database2.mdb].Table2
                cm.CommandText = "SELECT * INTO [C:\Temp\Database2.mdb;].Table2 FROM Table1"
                ' Also you can do this
                ' This will copy the data from [C:\Temp\Database1.mdb].Table1 to [C:\Temp\Database2.mdb].Table2
                ' but the destination table must exist in Database2.mdb
                cm.CommandText = "INSERT INTO [C:\Temp\Database2.mdb;].Table2 " &
                    "SELECT * FROM Table1"
            End Using

    Open in new window

    LVL 17

    Expert Comment

    by:Carlos Villegas
    In the last code, Database1.mdb is the source DB, and Database2.mdb the destination.
    LVL 16

    Expert Comment

    If you are just importing a table keep it simple, use the vb for import:

    DoCmd.TransferDatabase acImport, "Microsoft Access", "c:\test.mdb", acTable, "tab1", "tab2", False

    Run this from  M.mdb

    Author Closing Comment

    by:Jose Bredariol

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Have you ever wanted to restrict the users input in a textbox to numbers, and while doing that make sure that they can't 'cheat' by pasting in non-numeric text? Of course you can do that with code you write yourself but it's tedious and error-prone …
    I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
    Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
    This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

    760 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now