Solved

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

Posted on 2007-04-03
2
202 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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

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.
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…
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…
Suggested Courses
Course of the Month4 days, 22 hours left to enroll

635 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