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

Bill Benson bensonforums at gmail.com
Sat Dec 15 16:12:32 CST 2018


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
>


More information about the AccessD mailing list