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
Solved

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

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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
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…

856 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