alelacheur
asked on
INSERT data from one Access table to another Access table in VB.NET 2005
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=SmallGroupBookOfBu sinessSQL; 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\Encryp ted\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.adOpe nStatic, ADODB.LockTypeEnum.adLockR eadOnly)
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
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=SmallGroupBookOfBu
'Open a connection using the Microsoft Jet provider
Cnxn2 = New ADODB.Connection
Cnxn2.Provider = "Microsoft.Jet.OLEDB.4.0"
Cnxn2.Open("c:\data\Encryp
"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\
"Admin", "")
strSQL = "SELECT * FROM tblUpdates"
Rs.Open(strSQL, Cnxn2, ADODB.CursorTypeEnum.adOpe
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"
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
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER