[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 246
  • Last Modified:

SQL command using access database

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
0
Jose Bredariol
Asked:
Jose Bredariol
  • 3
  • 2
  • 2
2 Solutions
 
SheilsCommented:
IN A.mdb use

DoCmd.CopyObject "directory path\B.mdb", "tab2", acTable,"tab1"
0
 
Jose BredariolPMPAuthor Commented:
I´m using adodb. Like this :

Rs1 = New ADODB.Recordset
Xsql = "INSERT INTO ......."
Rs1 = getRecordset(Xsql)
0
 
SheilsCommented:
Where are you running this from tab1 or tab2
0
Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

 
Carlos VillegasFull Stack .NET DeveloperCommented:
VB.NET? try:
        Using cn As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Temp\Database1.mdb;")
            cn.Open()

            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"
            cm.ExecuteNonQuery()

            ' 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"
            cm.ExecuteNonQuery()
        End Using

Open in new window

0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
In the last code, Database1.mdb is the source DB, and Database2.mdb the destination.
0
 
SheilsCommented:
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
0
 
Jose BredariolPMPAuthor Commented:
Thanks
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 3
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now