473,718 Members | 2,191 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

how to check if table exists in Access with ADO?

G G
I need to check if a table exists in Access using ADO externally. I
tried

"Select Name from MSysObjects Where Name = 'myTable'"

with the ADO command object, but I got an error than I don't have
permission to read MySysObjects. Is there another way to check without
having to use On Error Resume Next?

Thanks,
GG

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #1
7 14524
ADO is only half the story. For information on the database schema, you need
the ADOX library so you can examine the Tables collection of the Catalog.

Unfortunately, ADOX is really half-baked and unreliable.

It should be possible to read MSysObjects though (unless you are blocked by
MDW security).

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"G G" <an*******@disc ussions.com> wrote in message
news:40******** *************@n ews.frii.net...
I need to check if a table exists in Access using ADO externally. I
tried

"Select Name from MSysObjects Where Name = 'myTable'"

with the ADO command object, but I got an error than I don't have
permission to read MySysObjects. Is there another way to check without
having to use On Error Resume Next?

Nov 12 '05 #2
G G
Thanks. Basically, I want to drop a table if it exists and then
recreate it. I guess On Error Resume Next it is.


*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3
G G <an*******@disc ussions.com> wrote in news:4097c919$0 $202$75868355
@news.frii.net:
I need to check if a table exists in Access using ADO externally. I
tried

"Select Name from MSysObjects Where Name = 'myTable'"

with the ADO command object, but I got an error than I don't have
permission to read MySysObjects. Is there another way to check without
having to use On Error Resume Next?

Thanks,
GG


You could try

TableExists = Not CurrentProject. Connection.Open Schema(adSchema Tables, _
Array(Empty, Empty, "Name of Particular Table")).BOF

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #4
"Allen Browne" <Al*********@Se eSig.Invalid> wrote in
news:40******** **************@ freenews.iinet. net.au:
ADO is only half the story. For information on the database schema, you
need the ADOX library so you can examine the Tables collection of the
Catalog.


Why do you say that?

--
Lyle
(for e-mail refer to http://ffdba.com/contacts.htm)
Nov 12 '05 #5
G G
There was actually one other thing I tried, along the same vein as ADOX.
I created a DropTable Function and a CreateTable Function. If the
DropTable function errors out I just resume next within the function and
exit out to the next call. Now, at least, the Resume Next's are
isolated within a function instead of all over the whole routine. I
can't figure out why I can't read MSysObjects with this:

strSql = "Select Name From MSysObjects Where Name = 'tbl1'"
cmd.CommandType = adCmdText
cmd.CommandText = strSql
cmd.Execute i

using acc97. will try later on a2k, a2002. In the meantime, the
functions seem to do the trick.

Thanks all for your replies.
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #6
Will the new table have the same structure as the one you're deleting? If
so, it may be easier to just delete all of the data in the table.

--
Wayne Morgan
Microsoft Access MVP
"G G" <an*******@disc ussions.com> wrote in message
news:40******** *************@n ews.frii.net...
Thanks. Basically, I want to drop a table if it exists and then
recreate it. I guess On Error Resume Next it is.

Nov 12 '05 #7
G G
Well, in this particular project, an end user will be selecting any
arbitrary (already existing) mdb file from an Open/Save Dialog box
(using api code) to pass specifically selected records from a
Non-Microsoft datasource. XYZ.mdb probably won't contain the temp table
tblxxxyyyzzz. But say the end user has already retrieved records from
the external datasource to the selected mdb file XYZ.mdb. If the end
user decides to select other records from the datasource to the same mdb
file, tblxxxyyyzzz will be dropped and a new tblxxxyyyzzz will be
re-created on the fly and the new selection of records will be
transferred to the table. If the end user wants to accumulate the
records, the records will have to be transferred to another table with
the same schema as tblxxxyyyzzz.

GG

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #8

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
11858
by: Jonathan | last post by:
I am looking for a simple way to check if a database table exists. I keep getting advice to use "Try.. Catch" and other error handling methods, but I obviously don't want to have to display an error message and stop the process every time someone loads the script after the table is created because that would mean the page could only ever run once which of course not the solution I was looking for. I simply want to know how I can check...
1
442
by: G G | last post by:
I need to check if a table exists in Access using ADO externally. I tried "Select Name from MSysObjects Where Name = 'myTable'" with the ADO command object, but I got an error than I don't have permission to read MySysObjects. Is there another way to check without having to use On Error Resume Next? Thanks,
3
14940
by: Anthony Bowman | last post by:
Hello, I have a access application that resides locally on users desk, I am writing a VB.Net interface that let's the users pull data down from a SQL server database and populate the local access database with that data, I need to check the local access database to see if there is already a table with the name the users give. Anyone run into that or have any idea how this would be done. Thanks Anthony
10
22761
by: Geoff Jones | last post by:
Hi I'm trying to drop a table by using: Dim cmd As New OleDbCommand("DROP TABLE IF EXISTS books", myconnection) cmd.ExecuteNonQuery() but I get a syntax error: "Syntax error in DROP TABLE or DROP INDEX"
9
7260
by: Carl Fenley | last post by:
I am successfully adding stored procedures to an Access database. However, I need to be able to check if the stored procedure of the same name already exists. Is there a way to do this other than waiting for the OleDbException caused when adding one that already exists? Here is the code snippet: Private Sub CreateStoredProcedures()
5
3084
by: Mike Charney | last post by:
I have an Access data project. In this project I have a form that when Access starts is loaded and this form has VBA code attached to it. One of the first things I need to do is check if a table called tblimport exists. If this table exists then I need to delete it. I have tried looking through the web but I can not find a simple solution to this. I do not want a separate module, just a couple lines of code. Does the table exist, Yes =...
2
6937
by: RLN | last post by:
Re: Access 2003 I have code to check to see if a table exists. There are several other tables (tblQ12, tblQ13, etc) that do already exist & this code runs fine; msgbox says the value returned is true when the table already exists prior to the run of this module. If the table does not exist, this line bombs varTemp = dbsTemp.TableDefs(prmTable).Name with this error:
6
1680
by: BrianDP | last post by:
I have a table that has always been in my back-end of this application. The table is getting quite large, and, on top of that, we lose records out of this table. They just dissappear! No rhyme or reason to it. I imported the table into SQL, and re-linked it back into the front end of the application. All the fields show. I had to pick a primary key, which is fine, it's a text field (nvarchar 255), and it is indeed a unique value. ...
16
5663
by: Brian Tkatch | last post by:
Is there a way to check the order in which SET INTEGRITY needs to be applied? This would be for a script with a dynamic list of TABLEs. B.
4
2650
by: jaishu | last post by:
Hi all, I am in the process of developing a small Access application. i have the table locally in Access and I am using the following code to get the recordcount ( or to check if any record exists), but when i check for rst.eof condition, it is always true..but when i actually run the query i see, many rows returned, i m not clear about how to specify the connection parameter with the recordset, as i m using the tables locally, but i...
0
8723
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9354
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
9120
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9053
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7987
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6652
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5971
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3182
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2122
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.