Hi

Actually i think i know how to create a table using SQL (not sure if the other's alternatives are beater or not), i haven´t try yet to add values to the columns but i guess it's easy as well.

What i have no idea how to do it is how to check if the table already exits ...

Thanks in advance

Hi Miguel,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

I think there are several ways to check if a table exists. For example, you can query the MSysObjects table, or you can use a TableDef object to loop through the TableDefs collection. Another way, maybe, is to just do a simple DoCmd.SelectObject and trap any error if the table doesn't exists.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

EDIT: Oops, looks like I was a few seconds too slow on the keyboard today.

This post has been edited by theDBguy: Apr 11 2012, 01:09 PM

Hi

It seems arnelgp is teaching how to do it using the "Microsoft DAO 3.6 Object library" to check if exists or not correct ?
I was thinking more not using any library, guess that way is using the DoCmd.SelectObject like theDBguy ...

I have try to search for table in the Microsoft Visual Basic help but it was not very helpful.

By the way i am using the 2010 office.
I will try to check with the DoCmd.SelectObject them i will use the library ...

Thanks in advance

Hi Miguel,

That's right! Arnel just showed you how to use DAO to silently check if a table exists.

Depending on your setup, using the SelectObject method is not a "silent" method. It will actually either "select" the table in the Nav Pane or expect that the table is already open.

Using a query against the MSysObjects table does not need DAO and can be "silent." For example:

If DCount("*", "MSysObjects", "[Name]='TableName'")>0 Then
MsgBox "Table Exists"
Else
MsgBox "Table Does Not Exist"
End If

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi

Is everything on access or on vba consider an object ?
I am asking this because you have use "MSysObjects" in the domain parameter on dcount function and i am just trying to understand th code you out up there...

Thanks in advance

Hi Miguel,


I guess, in a sense, they pretty much are. You could say that "everything" IS an object. But that's using the term very "loosely."



MSysObjects is a system table that Access use to keep track of certain objects in the database. Tables just happened to be one of them.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi

How do you know about "MSysObjects" ?
I am asking this because if we search for that word on the Microsoft Visual Basic help it does not return nothing...

How can we know about that and other's if there are not any reference to them in the help ?

Thanks in advance

To be honest, I am uneasy about using any of the hidden, system objects for operattions like this because they are NOT there for users to work with directly. Inadvertent changes to one of the MSysxxxx tables can cripple a database and render it unusable.

Therefore, if I have my druthers, I just don't touch them.

In this case, it is relatively safe because you are only doing a a read-only lookup from the MSysObjects table. Still, I'd lean towards another method, such as the DAO approach.

Hi

If i use the TableDef class how can i add values with it ?
Or do i use the same SQL command do add the values to the table ?

Thanks in advance...

This post has been edited by Miguel_A: Apr 12 2012, 01:04 PM

Hi

And another thing...
On that example up there using the TebleDef how can i use that in a if statement ?
I have already put that on my code but nothing happen ... just stop's there when arrive to the lines...

Thanks in advance

Hi,


To tell you the truth, I learned about it just like you did - somebody told me. (IMG:style_emoticons/default/grin.gif)

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi

Everything is working now about checking if table exits (using the TableDef) and i have figured out how to add values to the table as well...
About the checking if table exists the problem was that i have copy the code to the sub but that thing is a function and i have not noted that at the time.

Thanks for all

Hi Miguel,

(IMG:style_emoticons/default/yw.gif)

Congratulations! We are all happy to help.

Good luck with your project.