Inserting a new Access record from Excel

[BigPerm]

Does anyone know how to insert a record into an access table from excel automation? I tried opening access and running ( DoCmd.RunSQL strQ ), and I got no errors but no data seems to show up.

 

[scking]

This should be a relatively simple task using either DAO or ADO, but not necessarily concise. I've routinely built spreadsheets in Excel using ADO and the only difference would be that you would need to get the data from Excel and then AddNew record to the database.

rstRecordset.AddNew
rstRecordset!Field1 = vDataFromExcel
rstRecordset.Update

Ignore from where I create a worksheet on ---
Public Function CreateReportFromQueryADO(Optional DbPath As String, _
Optional SQL_Query As String) As Boolean

Dim cnnConn As ADODB.Connection
Dim rstRecordset As ADODB.Recordset
Dim cmdCommand As ADODB.Command
Dim ws As Worksheet
Dim iCols As Integer

On Error Resume Next

' Open the connection.
Set cnnConn = New ADODB.Connection
'With cnnConn
' .ConnectionString = _
' "Provider=Microsoft.Jet.OLEDB.4.0"
' .Open DefaultDbPath
'End With

' If the DbPath was provided verify it
' otherwise open from the default database.
If Len(DbPath) > 0 Then
' Check to verify that the database exists at the location stated
' in the input parameter
If Len(Dir(DbPath)) > 0 Then
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open DbPath
End With
Else
MsgBox "Invalid database path (" & DbPath & &quot"
Exit Function
End If
Else
If Len(Dir(DefaultDbPath)) > 0 Then
With cnnConn
.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0"
.Open DefaultDbPath & ";UID=guest;PWD="
End With
Else
MsgBox "Invalid database path (" & DefaultDbPath & &quot"
Exit Function
End If
End If

On Error GoTo HandleErr

' Set the command text.
Set cmdCommand = New ADODB.Command
Set cmdCommand.ActiveConnection = cnnConn
With cmdCommand
.CommandText = SQL_Query
.CommandType = adCmdText
.Execute
End With

If cnnConn.Errors.Count > 0 Then
Err.Raise 10621, "cnnConn", "Error Returns following execute."
End If

' Open the recordset.
Set rstRecordset = New ADODB.Recordset
Set rstRecordset.ActiveConnection = cnnConn
rstRecordset.Open cmdCommand

' If there are no records then just notify the user
' and exit the procedure.
If rstRecordset.EOF Then
MsgBox "There are no records for this request. Please " _
& "review your request to ensure it is correct."
Exit Function
Else
CreateNewSheet
End If

'Worksheets.Add Count:=1, Before:=Sheets(1)
Set ws = Worksheets(1)

For iCols = 0 To rstRecordset.Fields.Count - 1
ws.Cells(1, iCols + 1).Value = rstRecordset.Fields(iCols).Name
Next

ws.Range(ws.Cells(1, 1), _
ws.Cells(1, rstRecordset.Fields.Count)).Font.Bold = True
ws.Range("A2&quot.CopyFromRecordset rstRecordset

Exit_Proc:
On Error Resume Next
' Close the connections and clean up.
cnnConn.Close
Set cmdCommand = Nothing
Set rstRecordset = Nothing
cnnConn.Close
Set cnnConn = Nothing
Exit Function

HandleErr:
Select Case Err.Number
Case Else
Call HandleTheError("", "frmReportGenerator.CreateReportFromQueryADO", Err)
End Select

Resume Exit_Proc
Resume
End Function


----------------------
scking@arinc.com
Life is filled with lessons.
We are responsible for the
results of the quizzes.
-----------------------

 

[BigPerm]

Thanks for the code. I ended up just calling a procedure in access from excel with parameters I wanted and the procedure executed in access.