checking if a table exists

You should to loop through tabledefs collection and compare each element's name with your table's name. If the name founed, then delete a table.

I don't know if it's the nicest way but you could do something like:

Dim dbs As Database
Dim rst As Recordset
Dim strSQL As String

Set dbs = CurrentDb
strSQL = "SELECT * FROM YourTable"

On Error Resume Next

Set rst = dbs.OpenRecordset(strSQL, dbOpenDynaset)

If Err.Number = 3078 Then
    MsgBox "Table Not Found!"
Else
    MsgBox "Table Found!"
End If

On Error GoTo 0


Error 3078 is the error which is generated when the table is not found.

Are you making a table in the traditional sense, i.e. in a multi user enviroment, assuming a traditional front-end and back-end environment?

It shouldn't matter if a table exists on the client machine. The table is not accessible to other clients and can be changed anytime by the client. Just perform your query, delete the table if exists and start withthe new data.

One of possible solutions:

Dim strTableName As String
strTableName = "customer"
If DCount("*", "MSysObjects", "Type=1 and Name=" & Chr(34) & strTableName & Chr(34)) = 1 Then
    MsgBox strTableName & " exists"
Else
    MsgBox strTableName & " not exists"
End If

Cheers,
Dedushka

I make a table with a 'make table query'.
Since i use it to generate reports, and more users should be able to generate a report on the 'same' time, another user has to wait till the table is deleted, to create it's own again.

It's just a temporary table to store the combined data you need (select) to generate your own word document.

I start trying the
If Err.Number = 3078 Then
    MsgBox "Table Not Found!"
Else
    MsgBox "Table Found!"
End If

 method.
Justus.

jbrugman, my question, are you "making" the table on the client machine in the traditional sense, i.e., front end, back end database app. If so, your data is static to the client.

Am I missing something here???

Untested from the top of my head:

dim MyDB as database
Dim MyTable as table

set mydb = currentdb
for each mytable in mydb.tabledefs
  If Mytable = "The Table You Search" Then
    msgbox("Found it!")
    exit for
 End if
next

set mytable = nothing
mydb.close
set mydb=nothing

..
If Mytable.Name = "The Table You Search" Then
....

jBrugman, try to get a grip on CareyMBilyeu's comment.

For robustness and high performance, put temporary tables on a 'local client' database, and so do away with otherwise much greater multiuser issues.

From one hand if you'll put your temporary tables on a 'local client' database, this front-end databases will grow and it is necessary to compress them from time to time.
From the other hand, you need not to check at all if a table exist, just because "table make query" itself doing this and if the table exist - delete it before creating new one.

A different approach would be to use the SQL from your make-table query; then do your test to find out if a table exists (say, tblCustomer). If it does, increment a variable and create "tblCustomer1" or some such . . . until the table doesn't exist. When the user goes to open the report, open it in design view and set the recordsource to the currently-used table name. Then, on the report's close event, you could do a

currentdb.tabledefs.delete me.recordsource

or do a loop through the tabledefs every time the app closes, and delete all tabledef objects that start with "tblCustomer" or some such.

Just some ideas . . .

brewdog

Brewdog: The problem then arise in a multi-user environment on whether this would affect other users opening of the report.
You can actually change the recordsource of the report in the OnOpen event, this would not affect others and would make this a possible solution.

I think I like the Append method better.

jbrugman, one other comment about your statement:

"I make a table with a 'make table query'.
Since i use it to generate reports, and more users should be able to generate a report on the 'same' time, another user has to wait till the table is deleted, to create it's own again.

It's just a temporary table to store the combined data you need (select) to generate your own word document."

If you are using the table for a report only, why not base the report simply on a standard select query instead of a make table query?

Carey

Carey: A good question if it is just the one query. If on the other hand this is a series of queries resulting in a recordset that is suitable for the report, starting with the make table then this might be the only way.

Then whynot a Union Query??, were not changing any data here are we??

CB

Union queries are nice, BUT:

1) They are extremely unreadable
2) They perform very poorly since they can't make use of indexes etc.
3) You can collect info from several source using an Union, but you can't do mutliple operations on the same set of data. i.e. If you need to perform calculations etc that needs to be taken in steps, Unions can't help you.

CareyMBilyeu ,
 
jbrugman, my question, are you "making" the table on the client machine in the traditional sense, i.e., front end, back end database app. If so, your data is static to the client.

Am I missing something here???
 
 
--> Well, i'm just making the database in access, and i'm not sure how to make a front end (etc) database in any programm unless using odbc with perl
eg. front end is ie or nn browser,
middle ware is perl
back is any database.
but..
In this case, i'm just working on a database located on the server, that can be accessed from any machine on the network by starting access and the mdb file.

I would not know how to make a table on the client side.

Justus

Ok, thanks for all your input, but i found out that Trygve 's comment worked best for me.

Trygve, i'm still curious about one thing.
How do you make a good structure for multi-user databases in access, since i thought access allways supported multi user, using locks and (non) exclusive data.

Justus

Access handles multi-users well. The problem arrise if you try to manipulate temporary objects in a shared file without allowing the possibility of two or more users wanting to access the object at the same time. This is why using a make table to create a temp table is no good, but you need to add a login field and use append/delete queries against a fixed temp table instead.

When it comes to performance etc. this is the best setting.

Table are put in one MDB (or several if the amount of data or tables get large). These tables are linked to the application mdb which is compiled to a MDE file and distributed locally to the users PCs. The compiled MDE will run faster than MDBs and does add some security since your users cannot tamper with the objects.

Access should be installed locally and not on some network drive !!!


The benefits:

- Faster performing application
- Less network traffic
- Happier users (hopefully)

Drawbacks:

- Updated MDEs need to be distributed. Some script that checks a central ini file which contains a revisions number against the last downloaded version usually does the trick. You network admin can probably help you with this.
- You need to compile after having made changes. This takes some time and forces you to distribute. On the other hand this will force you to test more before releasing your latest "fix"