awolarczuk
asked on
How to see if a table is already there in a access database from VB 2005
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
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))
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.
Use GetSchema - if you use OleDb, OleDbConnection.GetSchema,
ASKER
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
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
ASKER
alex_paven
thanks mate could you please give me a code example with what i have aboce
thanks mate
thanks mate could you please give me a code example with what i have aboce
thanks mate
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
ASKER
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
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.OleDbCon nection 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.
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()
ASKER
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
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.
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.
Too funny !!!!!
mx
mx
ASKER
Mx
mate is there any way u can help me here i am so stuck mate all the point would be ours
mate is there any way u can help me here i am so stuck mate all the point would be ours
ASKER
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
this is the error i am getting when i run the code
Sorry, initially I thought you were working in Access ... but I see it's .Net.
mx
mx
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.
ASKER
mate i dont them showing up in access 2007 is there a way to show then
Right click navigation pane, display options, show hidden and system objects.
Sorry, right click -> navigation options -> display options, show hidden and system
ASKER
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
found it mate but when i true to read from it i get the above
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Basic it is telling me that this is a system table and cant be read
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