[AccessD] How to write VBA code to connect and run queries from excel

[Paul Hartland]

Just as a test as I am not near anything I can try this on, try changing

If oConn.State = 1 Then
    oCmd.ActiveConnection = oConn
    strSQL = "Select * from tblData_OIT"
    Set oRst = New ADODB.Recordset
    oRst.Open ("Select * from tblData_OIT")  'Error 3709
'This is not working either on the ,Execute line
    'Set oCmd = New ADODB.Command
    'oCmd.CommandType = adCmdText
    'oCmd.CommandText = strSQL
    'Set oRst = oCmd.Execute() 'Error 3709
End If

To
If oConn.State = 1 Then
    oCmd.ActiveConnection = oConn
    strSQL = "Select * from tblData_OIT"
    oCmd.CommandType = adCmdText
    OCmd.CommandText = strSQL
    Set oRst = oCmd.Execute() 'Error 3709
End If

PAUL


On Sat, 15 Dec 2018, 20:49 Bill Benson <bensonforums at gmail.com wrote:

> I know this is pretty basic stuff. I am just having a mental block. I want
> to have an excel "front end" to an access database that solution will work
> if the end user does not have MS Access installed. I saved the access
> database as .ACCDE and am using this code to test. oConn state = 1 do I
> suspect the connection is open, but I get the Error 3709 "the connection
> cannot be used to perform this operation. It is either closed or invalid in
> this context". I have googled this ad nauseum and nothing stands out that I
> have done incorrect, and no one says that it should not work on a accde
> file. Tried on accdb as well, also same error. All variables have state, so
> it is just the setting of oRst or attempt to execute the adodb command that
> is failing. I think I am using an appropriate provider string, but maybe
> not? Also, can someone confirm that, were I to be able to get this to run
> eventually, that I can use Excel front end and Access ACCDE (or ACCDB)
> backend without the end user having anything other than MS Office (without
> Access) and Windows 10?
>
> Sub TestADODB(strPath As String)
> Dim strConnect As String
> Dim oCmd As New ADODB.Command
> Dim oRst As ADODB.Recordset
> Dim oConn As ADODB.Connection
> Dim strSQL    As String
>
> strConnect = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strPath &
> ";Jet OLEDB:Database Password=Access;"
> Set oConn = New ADODB.Connection
>
> 'Set oCmd.ActiveConnection = oConn
> oConn.Open strConnect
> If oConn.State = 1 Then
>     oCmd.ActiveConnection = oConn
>     strSQL = "Select * from tblData_OIT"
>     Set oRst = New ADODB.Recordset
>     oRst.Open ("Select * from tblData_OIT")  'Error 3709
> 'This is not working either on the ,Execute line
>     'Set oCmd = New ADODB.Command
>     'oCmd.CommandType = adCmdText
>     'oCmd.CommandText = strSQL
>     'Set oRst = oCmd.Execute() 'Error 3709
> End If
> End Sub
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>