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
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
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};D ATABASE={S QLdatabase Name}"
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("{PassT hroughQuer y}") '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};D ATABASE={S QLdatabase Name}"
QDrun.ReturnsRecords = True 'This should be True'
QDrun.ODBCTimeout = 15
QDrun.Close
HTH, DGordo63
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};D
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("{PassT
QDrun.SQL = SQLstring
QDrun.Connect = "ODBC;Driver={SQL Server};Server={ServerName
& "UID=sa;PWD={saPassword};D
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
If your stored proc takes no parameters, jsut ignore all reference to (.Parameters.Append )
Regards Alan
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
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
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
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
...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
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
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
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
Dim CN As ADODB.Connection
Set CN = New ADODB.Connection
CN.Open {ConnectString}
CN.Execute "exec sp_test2"
CN.Close
Set CN = Nothing