Solved

INSERT data from one Access table to another Access table in VB.NET 2005

Posted on 2007-04-03
2
197 Views
Last Modified: 2013-11-25
I am trying to insert data from one MS Access table to another MS Access table in a different db using VB.NET 2005.  Below is the code I have written, but I continue to get an syntax error when executing the code at "Cnxn3.execute(strSQL).

Here is the code:
        Dim Cnxn2 As ADODB.Connection
        Dim Cnxn3 As ADODB.Connection
        Dim strSQL As String
        Dim Rs As New ADODB.Recordset
        Dim connectionString As String = "Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=SmallGroupBookOfBusinessSQL;Data Source=SGBOBT, 1435"

        'Open a connection using the Microsoft Jet provider
        Cnxn2 = New ADODB.Connection
        Cnxn2.Provider = "Microsoft.Jet.OLEDB.4.0"
        Cnxn2.Open("c:\data\Encrypted\plan sponsor module\Plan_Sponsor_Module.mdb", _
        "Admin", "")

        'Open a connection using the Microsoft Jet provider
        Cnxn3 = New ADODB.Connection
        Cnxn3.Provider = "Microsoft.Jet.OLEDB.4.0"
        Cnxn3.Open("\\Winp-oa-100\winplnspmd\plan sponsor module admin\Plan_Sponsor_Module.mdb", _
        "Admin", "")

        strSQL = "SELECT * FROM tblUpdates"
        Rs.Open(strSQL, Cnxn2, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockReadOnly)

        If Rs.State = 1 Then
            If Rs.RecordCount > 0 Then
                Do Until Rs.EOF
                    strSQL = "INSERT tblUpdates (PSUID) "
                    strSQL = strSQL & "VALUES("
                    If IsDBNull(Rs.Fields("PSUID").Value) Then
                        strSQL = strSQL & "NULL)"
                    Else
                        strSQL = strSQL & Rs.Fields("PSUID").Value & ")"
                    End If
                     Cnxn3.Execute(strSQL)
                    Rs.MoveNext()
                Loop
            End If
        End If
        Rs.Close()
        Cnxn3.Close()
        Exit Sub
0
Comment
Question by:alelacheur
2 Comments
 
LVL 12

Accepted Solution

by:
Mamtha1982 earned 125 total points
ID: 18844291
I am not very well in classic ADO.
but i found that your insert statement is wrong..
try this...
strSQL = "INSERT INTO tblUpdates (PSUID) "
0
 

Author Comment

by:alelacheur
ID: 18845373
That worked!  Thank you so much!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…

685 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