Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

How to see if a table is already there in a access database from VB 2005

Posted on 2008-10-11
20
Medium Priority
?
211 Views
Last Modified: 2013-11-26
Hi guys
I would like to know how i check if a table is already there in a access database  and if its not create it, to creat it i am going to use the below code, but first i need to know if the table exists

Thanks for your help  
CREATE TABLE Job_List
(ID integer PRIMARY KEY ,Joblist char(255))
 
CREATE TABLE Update
(ID integer PRIMARY KEY ,Joblist char(255))

Open in new window

0
Comment
Question by:awolarczuk
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 7
  • 4
20 Comments
 
LVL 75
ID: 22696451
 Basically this :

 Dim db As DAO.Database
   Dim td As DAO.TableDef
   Set db = CurrentDb()
   On Error Resume Next
Set td = db.TableDefs("Job_List")
   If err.Number = 0 Then              ' Table exists
       CREATE TABLE Update (ID integer PRIMARY KEY ,Joblist char(255))

    Else        ' Table does Not exizst
       CREATE TABLE Job_List  (ID integer PRIMARY KEY ,Joblist char(255))
        CREATE TABLE Update (ID integer PRIMARY KEY ,Joblist char(255))
   End If


mx

0
 
LVL 6

Expert Comment

by:alex_paven
ID: 22696476
On error resume next? DAO? No offense, but yuck.
Use GetSchema - if you use OleDb, OleDbConnection.GetSchema, but it's the same instruction for every type of database. That function returns a datatable you can inspect; step through the code and look at the table to get an idea about all the info contained. Basically, since you'll want table information, use GetSchema("tables"), which returns a DataTable containing detailed information about all tables in your database, after which it's easy to check if one exists. GetSchema() without a parameter returns all the possible arguments you can give it. Look at "Working with the GetSchema Methods" page in the documentation.
0
 

Author Comment

by:awolarczuk
ID: 22696478
I think we are almost there

How it will work is this

the create code i have given you is the first two database i need but the next time i run the update it needs to check if that table is already in the database

something like

if job_list is not there then
 CREATE TABLE Job_List  (ID integer PRIMARY KEY ,Joblist char(255))
else
if update is not there then
CREATE TABLE Update (ID integer PRIMARY KEY ,Joblist char(255))
else


as i need to make changes for the database more then more updates will be run though meaning that my code will need to check if they are already there , when the update table is created i will use this as well to add a note that Joblist created

thanks

0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:awolarczuk
ID: 22696479
alex_paven

thanks mate could you please give me a code example with what i have aboce

thanks mate
0
 
LVL 6

Expert Comment

by:alex_paven
ID: 22696499
Ok then:

' assuming db is your OleDbConnection
Dim dt As DataTable = db.GetSchema("Tables")
 
  Dim results() As DataRow = dt.Select("table_name='Job_list'")
  If results.Length = 0 Then 'if not found
    'create "job_list"
  End If
 
  results = dt.Select("table_name='Update')
  If results.Length = 0 Then 'if not found
    'create "update"
  End If

Open in new window

0
 

Author Comment

by:awolarczuk
ID: 22696523
Alex this is going to sound so very nieve of me but what do you mean when you say OleDbConnection, do you mean the database connect or the recordset, as when i used DB i didn't get anything
0
 
LVL 6

Expert Comment

by:alex_paven
ID: 22696541
It depends on what method you used when connecting to the database; I assumed you used the proper .net way and used a System.Data.OleDb.OleDbConnection to connect, like in the code below.

Since you mentioned recordset, I understand you're using the old ways, something like DAO; if so, consider using the new methods, as they bring a ton of new features and ease of use.

Dim db as New System.Data.OleDb.OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=db1.mdb")
db.Open()
 
'alternatively, you could use Odbc.OdbcConnection:'
 
Dim db as New System.Data.Odbc.OdbcConnection("Driver={Microsoft Access Driver (*.mdb)};DBQ=db1.mdb");
db.Open()

Open in new window

0
 

Author Comment

by:awolarczuk
ID: 22696543
ok this is the code i have and i am still having issues with the dt sorry mate
 Public Sub dbu()
        Dim rs As New ADODB.Recordset
        Dim dt As DataTable = db.GetSchema("Tables")
        Dim sql As String
        Dim results() As DataRow = dt.Select("table_name='update'")
 
        If results.Length = 0 Then 'if not found
 
            sql = "CREATE TABLE Update (ID integer PRIMARY KEY ,Update char(255))"
            Debug.Print(sql)
            rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
 
        End If
 
 
  results = dt.Select("table_name='Job_list')
        If results.Length = 0 Then 'if not found
            sql = "CREATE TABLE Job_List (ID integer PRIMARY KEY ,Joblist char(255))"
            Debug.Print(sql)
            rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
        End If

Open in new window

0
 
LVL 6

Expert Comment

by:alex_paven
ID: 22696547
The methods I suggested won't work with ADODB, sorry. If you're determined to use ADODB, I'm afraid I can't help. That's the old VB6 way of doing things and frankly it sucks.
There may be a way of doing things with ADODB: get the system tables of the database, one of which contains info on the structure of the database. But since I've pretty much forgotten everything I once new about ADODB, I'm afraid I can't help.
0
 
LVL 75
ID: 22696566
Too funny !!!!!

mx
0
 

Author Comment

by:awolarczuk
ID: 22696576
Mx
mate is there any way u can help me here i am so stuck mate all the point would be ours
0
 

Author Comment

by:awolarczuk
ID: 22696579
Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

this is the error i am getting when i run the code
0
 
LVL 75
ID: 22696598
Sorry, initially I thought you were working in Access ... but I see it's .Net.

mx
0
 
LVL 6

Expert Comment

by:alex_paven
ID: 22696783
Sigh... here's a further suggestion. In Access you have the option to show 'system tables', have a look at them - they have names starting with 'MSys' - and especially the one that contains table information, then query it (like you would query a normal table) for the tables you are looking for; that's pretty much the easiest way, I think.
0
 

Author Comment

by:awolarczuk
ID: 22696791
mate i dont them showing up in access 2007 is there a way to show then
0
 
LVL 6

Expert Comment

by:alex_paven
ID: 22696798
Right click navigation pane, display options, show hidden and system objects.
0
 
LVL 6

Expert Comment

by:alex_paven
ID: 22696799
Sorry, right click -> navigation options -> display options, show hidden and system
0
 

Author Comment

by:awolarczuk
ID: 22696844
Record(s) cannot be read; no read permission on 'MSysNavPaneGroups'.

found it mate but when i true to read from it i get the above
0
 
LVL 75

Accepted Solution

by:
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 1500 total points
ID: 22698108
"In Access you have the option to show 'system tables',"

I gave the typical method to determine this in Access.  Of course, you could add additional error trapping to include the case where the table IS there, but some other error occurs.

mx
0
 

Author Comment

by:awolarczuk
ID: 22698404
Basic it is telling me that this is a system table and cant be read
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question