Link to home
Start Free TrialLog in
Avatar of acgt
acgt

asked on

calling a SQL Server stored procedure from MS access

Hi,

I am trying to call a simple stored procedure from code in Access 2000 to SQL Server. I've tries using a query def but cant seem to get the query def connection going, or running it as a simple passthrough (CurrentDb.Execute SQLstring, dbSQLPassThrough). I've spent the whole day on this knowing that it sould be easy. Access documentation is useless and I cant seem to find anything that works. Help would be really appreciated.

Its a standard Access 2000 database with linked tables to a SQL Server 2000. To ensure that its not a rights issue Im logged on as dbo (sa).

The two options I've tried (cut down somewhat. 1)

    Dim SQLstring As String
    Dim QDrun As QueryDef
     SQLstring = "exec sp_test2"    'A VERY SIMPLE update SP, tried without the 'exec'

   Set QDrun = CurrentDb.CreateQueryDef("")
   QDrun.ReturnsRecords = False
   QDrun.SQL = SQLstring
   QDrun.Connect =           'CANT GET THIS CORRECT, need to get it from the currentdb??
   QDrun.ODBCTimeout = 15
   QDrun.Execute dbSQLPassThrough

2)

    Dim SQLstring As String
     SQLstring = "exec sp_test2"                                          'A VERY SIMPLE update SP, tried without the 'exec'
    CurrentDb.Execute SQLstring, dbSQLPassThrough

The error message I get here is "Invalid SQL Statement, expected 'DELETE', 'INSERT', 'Procedure', 'SELECT or Update'"

This is really doing my head in so any help would really be appreciated.

Thanks
Avatar of TitoBob
TitoBob
Flag of United States of America image

Have you tried ADO?

Dim CN As ADODB.Connection
Set CN = New ADODB.Connection
CN.Open {ConnectString}
CN.Execute "exec sp_test2"
CN.Close
Set CN = Nothing
Avatar of DGordo63
DGordo63

Try this.

   Dim SQLstring As String
   Dim QDrun As QueryDef
    SQLstring = "exec sp_test2"    'A VERY SIMPLE update SP, tried without the 'exec' 'You shouldn't need the exec'


  Set QDrun = CurrentDb.CreateQueryDef("")
  QDrun.SQL = SQLstring
  QDrun.Connect = "ODBC;Driver={SQL Server};Server={ServerName};" _
        & "UID=sa;PWD={saPassword};DATABASE={SQLdatabaseName}"
  QDrun.ReturnsRecords = True 'This should be True'
  QDrun.ODBCTimeout = 15
  QDrun.Close

Although I find that it is simpler to create the pass-through query first and then call it out here like this:

   Dim SQLstring As String
   Dim QDrun As QueryDef
    SQLstring = "exec sp_test2"    'A VERY SIMPLE update SP, tried without the 'exec' 'You shouldn't need the exec'


  Set QDrun = CurrentDb.QueryDef("{PassThroughQuery}")  'Do this instead of creating query definition on the fly'
  QDrun.SQL = SQLstring
  QDrun.Connect = "ODBC;Driver={SQL Server};Server={ServerName};" _
        & "UID=sa;PWD={saPassword};DATABASE={SQLdatabaseName}"
  QDrun.ReturnsRecords = True 'This should be True'
  QDrun.ODBCTimeout = 15
  QDrun.Close

HTH, DGordo63
have alook at: https://www.experts-exchange.com/questions/20643686/Calling-a-Stored-Procedure-in-Access-Project.html#8704673

If your stored proc takes no parameters, jsut ignore all reference to (.Parameters.Append )

Regards Alan
Avatar of acgt

ASKER

In the 1st example my problem is that I dont manage the connection string (QDrun.Connect = 'CANT GET THIS CORRECT, need to get it from the currentdb??), its managed by Access when I link the tables to SQL Server. What I think i need is someway to get the connection string currently in use out of access or set up the connection in a different way (this sounds like it should be standard, very simple stuff).

In the second example do you know why Im getting that error message as its the easiest way to run a stored procedure (or so Im told).

Thanks
A Pass-Through Query connection is independent of the tables you link to SQL server.  They must be defined with a connection string or you will not connect at all.

The linked tables are managed by Access.  However, in order to run SQL stored procedures from Access you must know your connection string because you are now using a pass-through query.  The pass-through query does not connect you to the linked tables at all.  You have to provide that information to the program yourself.  The elements of a connection string are:

ODBC                                 'the connection type you are using.
Driver={SQL Server}         'the driver you need to use.
Server=<ServerName>      'the server name where your SQL database resides.
UID=sa                               'userid of the person connecting to SQL.
PASSWORD=<password>  'the SQL password you've given to sa.
DATABASE=<dbName>       'the database name where the stored procedure is located.

Notice that anything that is between "<..>" is information that you have to provide.  Doing this will set up your connect string.

The reason why your second example doesn't work is because Access is looking for the standard SQL statement to execute:  SELECT, DELETE, INSERT or UPDATE.  I don't believe this will work for SQL stored procedures.

Regards,  DGordo63
Avatar of acgt

ASKER

Thanks for this. However if I now need to use / manage my own connection string how do I log the user in just once. When the user queries a linked table ODBC will kick in and prompt them for the password, next I need to capture the password and user ID myself for pass-through queries......How can I ensure that the user only logs in once?

...Is it me or does this seem like it should be simple and obvious? I think I need a drink!

Thanks in advance

Adam
ASKER CERTIFIED SOLUTION
Avatar of DGordo63
DGordo63

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of acgt

ASKER

I feared it might be somehting like this....at first pass it dosent seem like an ideal solution but the more i think about it the more it seems to make sense (time I used asp instead!). Thanks for this, will give it a go. As for the question of the login screen to update the table links....I look forward to hearing about that!

cheers

Adam
Thank your for the acceptance of the answer.

With regards to your logon screen for SQL, how do you let users get into your database?  Do you use a switchboard or some other menu-driven form?  Do they see the underlying tables and queries? Is your database secured?

DGordo63