Nick 'The Database Guy' wrote:

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).

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

On May 11, 7:26*am, Rick Brandt <rickbran... (AT) hotmail (DOT) com> wrote:
I was unsure whether it would execute on the server or the local PC so
thanks for clearing that up Rick.

On May 11, 1:25*pm, "Nick 'The Database Guy'" <nick... (AT) btinternet (DOT) com>
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.

Thanks,

Nick

Nick 'The Database Guy' wrote:
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.