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

[Bill Benson]

Got it. It certainly has been awhile since I have used ADO and Excel and
Access all at the same time.

https://support.microsoft.com/en-us/help/168336/how-to-open-ado-connection-and-recordset-objects

            has some alternatives to explore
Thanks again.

On Sat, Dec 15, 2018 at 5:21 PM Paul Hartland via AccessD <
accessd at databaseadvisors.com> wrote:

> 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
> >
> --
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>