Help with taking specific cell values from excel to access db via vba

[Sixxthson]

Hi,

Please help,

I'm having a problem with the code below.

Its supposed to take a select few cells data from the excel front end submission form and the input it one piece at a time into the relevent columns of an access db.

In the line .addnew its giving me runtime error 3251 application defined or object
defined error

Any ideas?

Sub Send2Access2()
Dim sNWind As String
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
        
              
    'Open Connection'
        sNWind = _
            "[URL="file://\\W2K6082\common\SHARED\ShiftSwap\Database2.accdb"]\\W2K6082\common\SHARED\ShiftSwap\Database2.accdb[/URL]"
        conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        sNWind & ";"
  
        conn.CursorLocation = adUseClient
            Set rs = conn.Execute("Shift_Swap", , adCmdTable)
   
    With rs
        rs.AddNew
            .Fields("Date Submitted").Value = Trim(Cells(50, 1).Text)
            .Fields("Agent Email").Value = Trim(Cells(50, 2).Text)
            .Fields("Date Requested").Value = Trim(Cells(50, 3).Text)
            .Fields("Payback Date 1").Value = Trim(Cells(50, 4).Text)
            .Fields("Payback Date 2").Value = Trim(Cells(50, 5).Text)
            .Fields("Shift start").Value = Trim(Cells(50, 6).Text)
            .Fields("Shift end").Value = Trim(Cells(50, 7).Text)
            .Fields("RDO").Value = Trim(Cells(50, 8).Text)
            .Fields("Call Type").Value = Trim(Cells(50, 9).Text)
            ' add more fields if necessary...
            .Update ' stores the new record

End With
rs.Close
conn.Close
End Sub

 

[BrianMH]

I believe you need to open the recordset with adodb. Try adding the below line before your with statement. Also you don't need the rs in front of the addnew since you are already referencing it using the with.

rs.Open

 

[Sixxthson]

I believe you need to open the recordset with adodb. Try adding the below line before your with statement. Also you don't need the rs in front of the addnew since you are already referencing it using the with.

rs.Open

Tried the above it now gives me an error on

rs.open

runtime errror 3705

Any suggestions?

Sub Send2Access2()
Dim sNWind As String
    Dim conn As New ADODB.Connection
    Dim rs As New ADODB.Recordset
        
              
    'Open Connection'
        sNWind = _
            "[URL="file://\\W2K6082\common\SHARED\ShiftSwap\Database2.accdb"]\\W2K6082\common\SHARED\ShiftSwap\Database2.accdb[/URL]"
        conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & _
        sNWind & ";"
  
        conn.CursorLocation = adUseClient
            Set rs = conn.Execute("Shift_Swap", , adCmdTable)
   
   rs.Open
   
    With rs
        .AddNew
            .Fields("Date Submitted").Value = Trim(Cells(50, 1).Text)
            .Fields("Agent Email").Value = Trim(Cells(50, 2).Text)
            .Fields("Date Requested").Value = Trim(Cells(50, 3).Text)
            .Fields("Payback Date 1").Value = Trim(Cells(50, 4).Text)
            .Fields("Payback Date 2").Value = Trim(Cells(50, 5).Text)
            .Fields("Shift start").Value = Trim(Cells(50, 6).Text)
            .Fields("Shift end").Value = Trim(Cells(50, 7).Text)
            .Fields("RDO").Value = Trim(Cells(50, 8).Text)
            .Fields("Call Type").Value = Trim(Cells(50, 9).Text)
            ' add more fields if necessary...
            .Update ' stores the new record

End With
rs.Close
conn.Close
End Sub

 

[BrianMH]

I'm not an expert on ADO but from your description it seems like the connection you are opening isn't of the correct type for the recordset. I'd check out the below links and make sure everything has the correct settings.

Open Method (ADO Connection)
Execute Method (ADO Connection)
Using the Connection Object's Execute Method

You could try changing

Set rs = conn.Execute("Shift_Swap", , adCmdTable)

to

Set rs = conn.Execute("Select * from Shift_Swap")

 

[Sixxthson]

I'm not an expert on ADO but from your description it seems like the connection you are opening isn't of the correct type for the recordset. I'd check out the below links and make sure everything has the correct settings.

Open Method (ADO Connection)
Execute Method (ADO Connection)
Using the Connection Object's Execute Method

You could try changing

Set rs = conn.Execute("Shift_Swap", , adCmdTable)

to

Set rs = conn.Execute("Select * from Shift_Swap")

Hi again, solved it now, had to effectively rewrite the connection from scratch. Just one problem remains:

.Fields("Shift Start") = Worksheets("Submission").Range("E15").Value

This like is placing a time value from excel in an access field and its coming out as numerical value instead, try i as i might i cant figure out how to format it to display as a time in access without getting some kind of error.

Any thoughts?

 

[BrianMH]

Should be able to just change the field type to time. Time is stored as a number but if you set it as a time field it will display as time.

You could try

.Fields("Shift Start") = cdate(Worksheets("Submission").Range("E15").Value)