Database Refresh window - Stored Proc - SQL server - ADO - Access 2000

[jdrulez]

Hello,

I have Access 2K and an .adp Project connected to SQLServer 7.
A stored procedure (ADO) creates a table and then open a report based on this table.
As the database window is not refreshed, the report can't be opened cause the table is not "seen".
(If I press F5, the report will be generated !)
I read a couple of article concerning the same problem and no one has the solution ...

thx
JD

 

[drctx]

i had the same problem about 2 months ago. this is what i came up with. it creates the stored procedure, refreshes the database connection and then runs the report using the new sp.


Dim strConnect As String
Dim rst As ADODB.Recordset ' might not need
Dim udl As MSDASC.datalinks
Dim conn As ADODB.Connection
Dim SQLstr As String

'----- create stored procedure and execute stored procedure
SQLstr = "CREATE PROCEDURE qrySelectSupplierSingleOpen AS SELECT * FROM dbo.DDRTable
DoCmd.RunSQL SQLstr

'----- update the sql db connection start

Set conn = New ADODB.Connection
Set udl = New MSDASC.datalinks
CurrentProject.OpenConnection "Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security Info=False;Initial Catalog=DDR-BE;Data Source=SFXFT234K4"

'----- update the sql db connection end

'----- run report
DoCmd.OpenReport "rptRelateSupplier to DDRSelectSupplierAllDDR", acViewPreview