Refresh Tables and Queries

[Chris Cairns]

I am running Access XP ADP and I create a temporary table on my SQL 2000
Server and sometimes I can access that table via code just after creation
and other times I can not.
I think I just need to refresh the list of tables but was not sure the best
method. Keep in mind that I am accessing the newly created table using code
and not the database window. I tried the tabledefs.refresh but get an error
438.

Something Like

Set dbs = Application.CurrentProject
dbs.TableDefs.Refresh

--OR--

Application.RefreshDatabaseWindow


What's the best method to handle this sort of thing?

 

[Michael, Cheng [MST]]

Hi Chris,

In my points of view, you'd better use SQL query to get table name and its
properties list from SQL Server 2000.

You could get your Base table by query like this
select * from information_schema.tables
where table_type = 'base table'

and You could get your Temp table by query like this
select * from tempdb.dbo.sysobjects

Thank you for your patience and cooperation. If you have any question or
concern, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[Michael, Cheng [MST]]

Hi Chris,

I am just checking on your progress regarding the information that was sent
you! I wonder have you successfully use SQL query to get table name and its
properties list from SQL Server as you wanted?

If you encounter any difficulty, please do not hesitate to let me know.
Please post here and let me know the status of your issue. Without your
further information, it's very hard for me to continue with the
troubleshooting.
Looking forward to hearing from you soon

Please feel free to post in the group if this solves your problem or if you
would like further help. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[Chris Cairns]

Actually - That would work to let me know if the object existed but for
something like a SP I would still be unable to execute the SP since Access
itself does not seem to know that it exists. That is why I was looking for
a way to refresh the Access tabledefs.

I know for example if I create a SP in another Access session and it will
not appear in an existing session until I do a View > Refresh OR F5. Then
the newly created tables and SP will appear.

If there is some way to force access to refresh it's list of known tables
and queries I think it will correct the problem. How would I go about
doing that using code?

 

[Michael, Cheng [MST]]

Hi Chris,

You could write tabledefs.refresh like this:
Sub ShowAllTables()
Dim dbs As DAO.Database
Set dbs = OpenDatabase("c:\db3.mdb")
Debug.Print dbs.TableDefs.Count

Dim tmpTD As TableDef
Debug.Print dbs.TableDefs.Count

For Each tmpTD In dbs.TableDefs
Debug.Print tmpTD.Name
Next tmpTD

End Sub
I got it pass in my machine and get all tables. If it doesn't works on your
machines, would you please show your code, that will give you error 438?
Detailed information that you can provide will make things clear and help
us move closer to the causes and resolutions.

What's more, you could have a look at KB:
ACC2000: Procedure to Create Data Sources and Relink ODBC Tables
http://support.microsoft.com/?id=208295
which will introduce you a way by relink or refresh you connections

Please feel free to post in the group if this solves your problem or if you
would like further help. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[Michael, Cheng [MST]]

Hi Chris,

You could also use Application.RefreshDatabaseWindow in this way
Private Sub Command0_Click()
'Requires reference to Office 9.0 object library.
Dim strSQL As String
Dim cBars As Office.CommandBars
Dim cBarCtl As Office.CommandBarControl
strSQL = "CREATE TABLE Test (ID varchar(5) PRIMARY KEY NOT NULL)"
CurrentProject.Connection.Execute strSQL
DoCmd.SelectObject acTable, , True

Set cBars = Application.CommandBars
Set cBarCtl = cBars.FindControl(msoControlButton, 3812)
If Not cBarCtl Is Nothing Then
cBarCtl.Execute
End If
End Sub

More detailed information can be get from KB:
ACC2000: RefreshDatabaseWindow Method Does Not Work in an Access Project
(ADP)
http://support.microsoft.com/?id=304256

Thank you for your patience and cooperation. If you have any question or
concern, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[Chris Cairns]

Keep in mind that it's an Access Project connecting to a SQL 2000 database.

I also receive the following message. Now I will receive this message again
and again but if I click on tables and then edit - Refresh or F5 and then
run the code again it works just fine. So it's certainly an issue with
access not knowing that the table or query exists.

3078 Microsoft Jet Database engine cannot find the input table or query.

 

[Michael, Cheng [MST]]

Hi Chris,


Based on my understanding, you could not use TableDefs.Refresh to update
your Access Project, asTabledef is a DAO object and it could be only used
by DAO instead of ADP.

As the KB:304256 told, RefreshDatabaseWindow method may do not work in an
Access Project . We'd better use workaround. I could make it on my machine
by following steps
1. Create a new ADP
2. Create a new Form, Add a command button
3. add On Click Event, select Code Builder
4. add Microsoft Office 11.0 Object Library from Tools -> Reference
5. add codes
Dim strSQL As String
Dim cBars As Office.CommandBars
Dim cBarCtl As Office.CommandBarControl
strSQL = "CREATE TABLE Test (ID varchar(5) PRIMARY KEY NOT NULL)"
CurrentProject.Connection.Execute strSQL
DoCmd.SelectObject acTable, , True

Set cBars = Application.CommandBars
Set cBarCtl = cBars.FindControl(msoControlButton, 3812)
If Not cBarCtl Is Nothing Then
cBarCtl.Execute
End If
6.Save All
Then, I craete a new table in SQL Server by Enterprise Manager
in my new ADP, Table List was not refreshed until I click the command
button on the form

I could not reproduce what you've said on my machine, would you please
create a new ADP and follow the steps I have listed.

Moreover, If the table you created is temp table, you may could not find it
in your ADP. In SQL Server, All temp table is created in tempdb, if your
ADP is connected to another database, you could not find temp table in your
database.

you could find B:304256 at http://support.microsoft.com/?id=304256 which
will give more detailed resolution about RefreshDatabaseWindow's not
working.

Thank you for your patience and cooperation. If you have any question or
concern, don't hesitate to let me know. We are here to be of assistance!

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.

 

[Michael, Cheng [MST]]

Hi Chris,

How are things going? I would appreciate it if you could post here to let
me know the status of the issue. If you have any questions or concerns,
please don't hesitate to let me know. I look forward to hearing from you,
and I am happy to be of assistance.

Sincerely yours,

Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.