February 3, 2011 at 12:47 pm
I need to call from an Access 2003 front-end a SQL stored-procedure (SP) that requires roughy 10 different parameters.
This procedure will be called N times (as many as rows in the source table), because it is used to import data into the SQL Database, originally in an Access table.
AFAIK, the only ways to do this would be: through VBA code (1), or using pass-through queries in Access (2). In both cases the iteration and how to pass the parameters are the biggest issues.
Can anybody give tips or help answering these questions:
1/ Best ways to call a procedure N times from Access. Alternative solutions more than welcome, if not just elaborate the ones proposed.
2/ Ways to repeat the call in Access and how to transfer the SP needed parameters (VBA, recordsets... etc)
February 3, 2011 at 3:02 pm
If your Access file is an adp connected to the SQL Server database, then just use VBA like this:
Dim rs As New ADODB.Recordset
rs.Open "EXEC [usp_MyProcedure] " & ID, CodeProject.Connection
If Not rs.EOF Then
Me!Description = rs!HeaderText
Me!txtLastDescription = rs!HeaderText
DoCmd.RunCommand acCmdSaveRecord
End If
rs.Close
The parameters are concatenated to the procedure in the string. This SP returns records, so I open a recordset and can curse through them.
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
February 3, 2011 at 4:08 pm
Thanks SSC-Enthusiastic, but I forgot to say there's no ADP project file. That complicates things a bit.
A second point worth considering is that the SP procedure called doesn't return any records: the goal is precisely to import data from an Access table to various SQL tables, by using the mentioned procedure.
February 4, 2011 at 7:22 am
If it's a database file instead of a project file, it's not any more complicated. But let me ask this: why send your data through parameter calls of a SP when you could just create a linked table to the SS database and do a table to table copy in Access?
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
February 4, 2011 at 7:38 am
It's a security issue. The way the SQL DB is built is not possible to simply link tables. The whole 'project' is split into 2 databases, one is accessible the other is not.
In this 2nd DB the data are transferred by SP => so you have to call them from Access to have all your data where needed.
The issue now is how to pass the SP parameters (preferably by using adodb.command, if not by using recordsets or pass-through queries) from an Access table to the SQL SP procedure that copies the data in the DB.
February 4, 2011 at 7:42 am
What is nice about an Access project is that it has a natural connection to SS so you can always just reference CodeProject.connection. But if it's an mdb, you can just create one and call the SP like this
Dim cn As New ADODB.Connection
cn.ConnectionString = <your connection string to SS>
cn.Open
cn.Execute "usp_InsertRecords " & Field1 & "," & Field2 & "," & Field3
cn.close
______________________________________________________________________________
How I want a drink, alcoholic of course, after the heavy lectures involving quantum mechanics.
February 4, 2011 at 10:00 am
Thanks toddasd, just one more question.
I'll assume I get the connection thing, but what isn't clear to me is, again, the parameters issue.
1/ First you'll need to iterate the process N times, 'cause the source is a table (meaning that you have to read it till reaching EOF), and therefore that should link to the source of the recordset?
2/ how does the SP know which parameter is which without, say, including a string of the type: @pmSurname, @pmID for the ID, etc?
I mean these call have to be somewhere in the SQL string that you build......
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply