refresh the database, queries window, or connection? 1

[drctx]

I've got a combo box to select from and then it creates a stored procedure and tries to open it.
I get this error message when i select an item from the combo box: "microsoft access can't find the object 'zTest'. when i look at the queries it's not there until i hit F5 to refresh and it appears. is there any way for this to work with out having to manually refresh the queries?


Private Sub Combo7_AfterUpdate()
Dim splr As String
splr = Combo7.Text
Dim SPname As String
Dim SQLstr As String

SQLstr = "CREATE PROCEDURE zTest AS SELECT * FROM _[Namelist - Suppliers] WHERE [Supplier:] = '" & splr & "'"
SPname = "zTest"
Me.Refresh
Text5 = SQLstr
DoCmd.RunSQL SQLstr
DoCmd.OpenStoredProcedure "zTest", acViewNormal, acEdit

End Sub

 

[chickey]

Try refreshing the querydefs collection - this
would then show the new query as existing:

CurrentDb().QueryDefs.Refresh

 

[drctx]

i added the code CurrentDb().QueryDefs.Refresh before i open the stored procedure and i get the error message "Object variable or With block variable not set (Error 91)" and it highlights CurrentDb().QueryDefs.Refresh on debug.


Private Sub Combo7_AfterUpdate()
Dim splr As String
splr = Combo7.Text
Dim SPname As String
Dim SQLstr As String

SQLstr = "CREATE PROCEDURE zTest AS SELECT * FROM [Namelist - Suppliers] WHERE [Supplier:] = '" & splr & "'"
SPname = "zTest"
CurrentDb().QueryDefs.Refresh
Text5 = SQLstr
DoCmd.RunSQL SQLstr
DoCmd.OpenStoredProcedure "zTest", acViewNormal, acEdit

End Sub

 

[chickey]

Firstly, there is no point refreshing until you have
created the query, so put the refresh in after the
'DoCmd.RunSQL SQLstr'.

I'll check out your code when I get to my home PC which
has Access 2k on it - Access 97 here (love those
corporate software standards).

Regards...

 

[drctx]

i switched the refresh code to after the stored procedure is created. get the same error.
this is an access 2000 data project.

thanks for your help, this is frustrating.

 

[chickey]

I'm afraid I'm not going to be much help to you on this.
I'm not hugely familiar but tried to find some info
on it anyway. The only way I could find to refresh the
procedures collection is to use adox like so (which is not
how you want to do it):

Sub ProcedureRefresh()
    Dim cat As New ADOX.Catalog

    ' Open the Catalog
    cat.ActiveConnection = _ 
        "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=c:\Program Files\" & _
        "Microsoft Office\Office\Samples\Northwind.mdb;"

    ' Refresh the Procedures collection
    cat.Procedures.Refresh
End Sub

You can get to the procedures collection like so:

Application.CurrentData.AllStoredProcedures

but this does not give you a refresh method so is useless.

The only other item in any way close to this was to
refresh the database window but I suspect this would
do nothing for the underlying procedures collection,
like so:

Application.RefreshDatabaseWindow

So, I'm afraid I'm no help to you - maybe someone else can.

Regards...

 

[drctx]

thanks for you help. while reseaching currentdb() i found what i needed to refresh the sql db connection. here's the code.

Private Sub SelectSupplier_AfterUpdate()

On Error GoTo Err_SelectSupplier_Click

Dim strConnect As String
Dim rst As ADODB.Recordset
Dim udl As MSDASC.datalinks
Dim conn As ADODB.Connection
Dim splr As String
splr = SelectSupplier.Text
Dim SPname As String
Dim SQLstr As String
SQLstr = "CREATE PROCEDURE zTest AS SELECT * FROM [Namelist - Suppliers] WHERE [Supplier:] = '" & splr & "'"
SPname = "zTest"
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

DoCmd.OpenReport "z rptRelateSupplier to DDRSelectSupplierOpen", acViewPreview
Exit_SelectSupplier_Click:
Exit Sub

Err_SelectSupplier_Click:
MsgBox Err.Description
Resume Exit_SelectSupplier_Click

End Sub