Prev: access 2007 background on tabs
Next: Core Java developer - Lansing,MI
From: Nick 'The Database Guy' on 11 May 2010 06:09
Hello everybody,

I am trying to execute the following code.

Dim qdf As QueryDef
Set qdf = New QueryDef
With qdf
.ReturnsRecords = False
.sql = CurrentDb.QueryDefs("qryLowValueMakeTable").sql
.Connect = "ODBC;DRIVER={SQL
Server};SERVER=MyServer;DATABASE=MyDB;UID=MyUserID;PWD=MyPassword"
.Execute
.Close
End With

The code is falling over when it gets to the .Execute statement. In
the sql there are some VBA expressions, but it executes perfectly from
a DoCmd.RunSQL statement, however you must know the password and I
would rather that my users were not aware of such information. The
error I get is 3420, Object invalid or no longer set.

Any help on this would be appreciated.

Nick
From: Rick Brandt on 11 May 2010 02:26
Nick 'The Database Guy' wrote:

> Hello everybody,
>
> I am trying to execute the following code.
>
> Dim qdf As QueryDef
> Set qdf = New QueryDef
> With qdf
> .ReturnsRecords = False
> .sql = CurrentDb.QueryDefs("qryLowValueMakeTable").sql
> .Connect = "ODBC;DRIVER={SQL
> Server};SERVER=MyServer;DATABASE=MyDB;UID=MyUserID;PWD=MyPassword"
> .Execute
> .Close
> End With
>
> The code is falling over when it gets to the .Execute statement. In
> the sql there are some VBA expressions, but it executes perfectly from
> a DoCmd.RunSQL statement, however you must know the password and I
> would rather that my users were not aware of such information. The
> error I get is 3420, Object invalid or no longer set.
>
> Any help on this would be appreciated.

Queries written in Access-SQL will seldom run on a SQL Server. You have to
use SQL Server syntax (which will definitely NOT contain any VBA).

From: Nick 'The Database Guy' on 11 May 2010 08:25
On May 11, 7:26 am, Rick Brandt <rickbran...(a)hotmail.com> wrote:
> Nick 'The Database Guy' wrote:
>
>
>
>
>
> > Hello everybody,
>
> > I am trying to execute the following code.
>
> > Dim qdf As QueryDef
> > Set qdf = New QueryDef
> > With qdf
> >     .ReturnsRecords = False
> >     .sql = CurrentDb.QueryDefs("qryLowValueMakeTable").sql
> >     .Connect = "ODBC;DRIVER={SQL
> > Server};SERVER=MyServer;DATABASE=MyDB;UID=MyUserID;PWD=MyPassword"
> >     .Execute
> >     .Close
> > End With
>
> > The code is falling over when it gets to the .Execute statement.  In
> > the sql there are some VBA expressions, but it executes perfectly from
> > a DoCmd.RunSQL statement, however you must know the password and I
> > would rather that my users were not aware of such information.  The
> > error I get is 3420, Object invalid or no longer set.
>
> > Any help on this would be appreciated.
>
> Queries written in Access-SQL will seldom run on a SQL Server.  You have to
> use SQL Server syntax (which will definitely NOT contain any VBA).- Hide quoted text -
>
> - Show quoted text -

I was unsure whether it would execute on the server or the local PC so
thanks for clearing that up Rick.
From: Nick 'The Database Guy' on 11 May 2010 11:16
On May 11, 1:25 pm, "Nick 'The Database Guy'" <nick...(a)btinternet.com>
wrote:
> On May 11, 7:26 am, Rick Brandt <rickbran...(a)hotmail.com> wrote:
>
>
>
>
>
> > Nick 'The Database Guy' wrote:
>
> > > Hello everybody,
>
> > > I am trying to execute the following code.
>
> > > Dim qdf As QueryDef
> > > Set qdf = New QueryDef
> > > With qdf
> > >     .ReturnsRecords = False
> > >     .sql = CurrentDb.QueryDefs("qryLowValueMakeTable").sql
> > >     .Connect = "ODBC;DRIVER={SQL
> > > Server};SERVER=MyServer;DATABASE=MyDB;UID=MyUserID;PWD=MyPassword"
> > >     .Execute
> > >     .Close
> > > End With
>
> > > The code is falling over when it gets to the .Execute statement.  In
> > > the sql there are some VBA expressions, but it executes perfectly from
> > > a DoCmd.RunSQL statement, however you must know the password and I
> > > would rather that my users were not aware of such information.  The
> > > error I get is 3420, Object invalid or no longer set.
>
> > > Any help on this would be appreciated.
>
> > Queries written in Access-SQL will seldom run on a SQL Server.  You have to
> > use SQL Server syntax (which will definitely NOT contain any VBA).- Hide quoted text -
>
> > - Show quoted text -
>
> I was unsure whether it would execute on the server or the local PC so
> thanks for clearing that up Rick.- Hide quoted text -
>
> - Show quoted text -

Futher more it was a make table query and I really don't want it
generating tables on the server. All that I am looking for is a way
to bypass the request for a password, but I am not sure if this is
possible. If anyone has any hints or tips they would be gratefully
received.

Thanks,

Nick
From: Rick Brandt on 11 May 2010 14:00
Nick 'The Database Guy' wrote:
> Futher more it was a make table query and I really don't want it
> generating tables on the server. All that I am looking for is a way
> to bypass the request for a password, but I am not sure if this is
> possible. If anyone has any hints or tips they would be gratefully
> received.

Then just make it a basic SELECT statement in the sql that you set. If some
of the processing requires VBA then you can always make the passthrough be
very simple and then use that as the input to a regular Access query where
you CAN use VBA.

Of course your SQL server could use trusted connections if you have a domain
and then the user would not need to provide credentials.
 | 
Pages: 1
Prev: access 2007 background on tabs
Next: Core Java developer - Lansing,MI