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

[Paul Hartland]

No worries, I think the problem just above that

Set oRst = New ADODB.Recordset
>     oRst.Open ("Select * from tblData_OIT")  'Error 3709

Is that the recordset isn't looking at a connection.

Paul

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

> Perfect. Had not realized I was setting activeconnection property of oCmd
> before setting oCmd as new ADODB.Command.
>
> Thanks Paul.
>
> On Sat, Dec 15, 2018 at 4:09 PM Paul Hartland via AccessD <
> accessd at databaseadvisors.com> wrote:
>
> > 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
> > >
> > --
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>