Solved

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

Posted on 2007-04-03
2
199 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 12

Accepted Solution

by:
Praveen Kumar 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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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 …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
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…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

751 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