<comments inline>
fumanchu wrote:
I'm not really sure this will work because of the Bulk Insert - seems
to be an ODBC error thing. Thank you anyway
My suggested solution will run ANY SP that requires Parameters. What the
SP does is a separate issue. The SP runs directly in SQL Server, no ODBC
except to send the Passthrough SQL to the server.
For now I set up an Access Form for my user to load the three values
into a linked table on the server. I set up a SQL Server Agent task to
run every minute for an hour a day during peak season. Each time it
runs essentially it strips off the top row of the table without a
timestamp, sets the timestamp, and then feeds the three values to the
sp that loads the file into the batch table. When it finishes it sets
a different timestamp.
She can check her batches after a minute by reopening her form and by
looking for the timestamps.
Messy. Just awful, really. But it works.
I agree, messy. And unnecessary.
I don't have a way to set up a SQL Server client on her machine nor do
I have time to learn VBA. It's straight Access or nothing for this one
shot solution.
Without using VBA you won't be able to do a lot of things. It isn't that
complicated to learn the basics of it if you've ever done any
programming in any other language.
I can get the batch name and accounting period from pulldowns linked
to tables. Is there an easy way to get file names from a given
directory into a pulldown?
I don't know of a way that doesn't involve VBA. Again, the code is not
that complex, but there is code involved.
Any more Rube Goldberg ideas, anyone?
I personally try to make things simpler, but I don't always succeed. :{)
I've done this before by using a passthough query. In VBA I change the
.SQL for a saved Passthrough query that has the appropriate Connection
settings already and the Returns Records Property set to No. You change
the .SQL to the EXEC statement for your SP:
Air Code:
Dim qd as DAO.Querydef
Set qd = CurrentDB.Querydefs("myPassthrough")
qd.SQL = "EXEC mySP '" & paramvalue1 & "', '" & _
pramvalue2 & "', '" & paramvalue3 & "'"
qd.Execute
set qd=Nothing
I'll try to flesh this out a bit more now that I know you aren't
familiar with VBA.
Since you have already linked the SQL tables to the Access DB, I'll
assume you know what I mean by 'Connection'. So, from the Database
Window, go to the Queries Tab. Click 'New', then pick Design View. while
viewing the QBE grid, in the Menu, pick Query - SQL Specific -
Passthrough Query. You now have the SQL View of the query. View the
Properties Box. Click on the 'ODBC Connection String', then the button
with three dots. Make the same Connection that you did for the Linked
Tables. Then set 'Returns Records' to No (I'm assuming that the SP is
doing actions, not sending back records). Save the Query (for this
example I'm assuming it is called myPassthrough).
Next, go to Design View of the Form that has the controls that the user
will pick the Parameter values from. I'm assuming that the names of the
three controls are named; ParamControl1,ParamControl2, ParamControl3.
Create a Command Button (RunSPbutton). This will fire up the Wizard. You
can pick any process to setup the button, we will replace the code next.
In the Properties box click on - Events Tab - On Click Event - button
with three dots. You should now see the VBA code that the wizard setup
for you for whatever process you picked above. Its handy to leave the
Error trapping code there. Here is the code you will put there in place
of the Wizard Code:
Private Sub RunSPbutton_Click()
On Error goto Err_RunSPbutton_Click
Dim qd as DAO.Querydef
Set qd = CurrentDB.Querydefs("myPassthrough")
qd.SQL = "EXEC mySP '" & Me!ParamControl1 & "', '" & _
Me!ParamControl2 & "', '" & Me!ParamControl3 & "'"
qd.Execute
Exit_RunSPbutton_Click:
Set qd=Nothing
Exit Sub
Err_RunSPbutton_Click:
Msgbox Err.Description
Resume Exit_RunSPbutton_Click
End Sub
Then save it. The user will fill in the three controls and then click
the button, the SP will run on the server with the parameters.
Lets see how you do with this before getting into the VBA code for
loading a Combo with Filenames.
--
Bri