Coding for exporting data from a spreadsheet to a database

[hey yer barred]

Hi All!

I have this coding beloew that is meant to export some cell values from a spreadsheet to a database....but it does not work.

Private Sub CommandButton1_Click()

Dim db As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim sPath As String
Dim sSQL As String

sPath = "J:\My Documents\TestDB's\CreditCare\TestDB4CC.mdb"
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath
db.ConnectionString = sConn
db.Open
sSQL = "SELECT * FROM tblMain"
With rs
    rs.ActiveConnection = db
    rs.LockType = adLockBatchOptimistic
    rs.CursorType = adOpenKeyset
    rs.Open sSQL
        If rs.EOF = False Then
        rs.MoveLast
        End If
    rs.AddNew
    rs!Name = "Test"
    rs.Update
    rs.Close
End With

MsgBox "Done!", vbInformation, "Test For Credit Care"

db.Close
Set db = Nothing
End Sub

I dont have any debug errors....it goes through the code fine...but does not update the database!

As you can see, this is a test piece of coding, just to make sure it works before I make the proper version.

Can anyone help please!

 

[Richard Schollar]

Hi

I think you need to use a Server-side cursor and probably change the LockType too:

Sub Test()
Dim db As New ADODB.Connection 
Dim rs As New ADODB.Recordset 
Dim sPath As String 
Dim sSQL As String 

sPath = "J:\My Documents\TestDB's\CreditCare\TestDB4CC.mdb" 
sConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & sPath 
db.ConnectionString = sConn 
db.Open 
sSQL = "SELECT * FROM tblMain" 
With rs 
    .ActiveConnection = db 
    .LockType = adLockOptimistic 'changed
    .CursorType = adOpenKeyset 'new line
    .CursorLocation = adUseServer
    .Open sSQL 
        'If rs.EOF = False Then   ===unnecessary
        'rs.MoveLast                  ===unnecessary
        'End If                           ===unnecessary
    .AddNew 
    rs!Name = "Test" 
    .Update 
    .Close 
End With 

MsgBox "Done!", vbInformation, "Test For Credit Care" 

db.Close 
Set db = Nothing 
End Sub

 

[hey yer barred]

Worked like a gem.....cheers mate!