checking if a table exists

I make a table (dynamic)
Do stuff with it
Then delete the table.

But...

In a multi - user enviroment, it has to be like this.

See if the table exists,
yes ?
wait till deteted
Make the table

no?
Make the table.

How do i check if tha table exists?

Thanks in advance,
Justus
LVL 4
jbrugmanAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

DedushkaCommented:
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.
0
pietjepukCommented:
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.
0
CareyMBilyeuCommented:
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.
0
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

DedushkaCommented:
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
0
jbrugmanAuthor Commented:
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.
0
CareyMBilyeuCommented:
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???
0
TrygveCommented:
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
0
DedushkaCommented:
..
If Mytable.Name = "The Table You Search" Then
....
0
ozphilCommented:
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.

0
DedushkaCommented:
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.
0
TrygveCommented:
Dedushka: Thank you for trying to cover me, but I had made another mistake too :-)


Here is a version of the function that works:

Function DoesTableExist(pTableName)

Dim MyDB As Database
Dim MyTable As TableDef

Set MyDB = CurrentDb
For Each MyTable In MyDB.TableDefs
  If MyTable.Name = pTableName Then
    ' Msgbox ("Found it!")
    DoesTableExist = True
    Exit For
 End If
Next

Set MyTable = Nothing
MyDB.Close
Set MyDB = Nothing

End Function

To wait for the table to be deleted:

While DoesTableExist("Table Name") = True
  DoEvents
Wend

If this is a setting where you need to wait because you don't want to destroy other users currently running code or report etc. then use the above. If you are just making a temp table for some purpose then consider this;

What I normally do in an multi-user environment is to not run a make-table query if the table already exist. Something like this is better (in my opinion):

' English code syntax ;-)

If table does not already exist then
  Make table, remember to include a Login field that states which user who owns the records (Note 1)
Else
  Delete all records marked with current users login, these are leftovers from last run
  Append query to put in the new records, along with current users login.
End If

Rest of code



A good idea is to let the form, report or code that uses that newly added information "clean up" by deleting the records marked with my login, but the next run will also take care of this.

Note 1) If you are in a front end/back end environment, and are not comfortable with creating tables in the other database/server, then you should make sure that the table is already there and then skip the make table steps in the above setting.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
brewdogCommented:
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
0
TrygveCommented:
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.
0
CareyMBilyeuCommented:
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
0
TrygveCommented:
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.
0
CareyMBilyeuCommented:
Then whynot a Union Query??, were not changing any data here are we??

CB
0
TrygveCommented:
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.
0
jbrugmanAuthor Commented:
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
0
jbrugmanAuthor Commented:
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
0
TrygveCommented:
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"
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.