Export from Excel to Access ADODB

[javydreamercsw]

The following is part of a code in VBA adapted from a code i found in
this forum. It does great when creating the info but i want the code to
work as an update too. In other words it'll find if the record exists
if it does update otherwise create a new one. Any idea?

Dim cn As ADODB.Connection, ct As ADODB.Connection, found As Boolean,
rs As ADODB.Recordset, r As Long, sql As String
' connect to the Access database
If IsEmpty(Range("Información!B3")) Then
Beep
MsgBox "Falta el número de sección"
End
End If
If IsEmpty(Range("Información!B4")) Then
Beep
MsgBox "Falta el número del curso"
End
End If
If IsEmpty(Range("Información!B5")) Then
Beep
MsgBox "Falta el nombre del curso"
End
End If
If IsEmpty(Range("Información!B6")) Then
Beep
MsgBox "Falta la hora de reunión"
End
End If
If IsEmpty(Range("Información!D3")) Then
Beep
MsgBox "Falta el nombre del instructor"
End
End If
If IsEmpty(Range("Información!D4")) Then
Beep
MsgBox "Falta el término"
End
End If
If IsEmpty(Range("Información!D5")) Then
Beep
MsgBox "Faltan los días de reunión"
End
End If
Set cn = New ADODB.Connection
cn.Open "Provider=Microsoft.Jet.OLEDB.4.0; " & _
"Data Source=E:\UI_Database\Registro Database.mdb;"
' open a recordset
Set rs = New ADODB.Recordset
rs.Open "Sección", cn, adOpenKeyset, adLockOptimistic, adCmdTable
' all records in a table
r = 3 ' the start row in the worksheet
rs.Find "section = " & Range("Información!B3").Value
If rs.EOF Then 'No record in table with this ID
rs.AddNew
found = False
End If
rs.Fields("courseno") = Range("Información!B4").Value
rs.Fields("section") = Range("Información!B3").Value
rs.Fields("semester") = Range("Información!D4").Value
rs.Fields("instructor") = Range("Información!D3").Value
rs.Fields("time") = Range("Información!B6").Value
rs.Fields("days") = Range("Información!D5").Value
If found = True Then
rs.Save
Else
rs.Update
End If
' stores/updates the new record
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End

 

[javydreamercsw]

Since no answeres yet, I've been working with sql queries trying to fi
that. Dunno if someone knows about queries in VBA but here we go...

sql = "SELECT * FROM Sección WHERE courseno = '"
Range("Información!B4").Value & "'"
Set rs = cn.Execute(sql)
[COLOR = red] If Not rs.EOF Then 'No existe
sql = "UPDATE Sección SET section = '"
Range("Información!B3").Value & "' WHERE courseno = '"
Range("Información!B4").Value & "'"
MsgBox sql
Set rs = cn.Execute(sql) [/QUOTE]

The first query (select) works fine but when trying to do the Updat
query it always give me a syntax error. It seems like a right write
query to me. Same happens with this query:

[COLOR = red]sql = "INSERT INTO Sección (courseno,section) VALUES('"
Range("Información!B4").Value & "','" & Range("Información!B3").Value
"'"
Set rs = cn.Execute(sql)[/QUOTE]

Any Idea

 

[Jake Marx]

Please see reply to later thread you started on this subject.

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]