asked on

when checking if there is a table alreay in my database

HI all
I am trying to see if there is already a table in my database the guy that hav been trying to help me have been great but it seems that i am now getting another error

"Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another"

When the code runs

Below is my code thanks for the help in advance

my other question id is ID:23807380

Module update
    Public Sub dbu()
        connect()
 
        Dim rs As New ADODB.Recordset
        Dim dt As DataTable = cnn1.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)
            SplashScreen1.updateinfo.Text = ("Stars Datase Update (New Table,""Update"")")
            MsgBox("Stars Datase Update (New Table,""Update"")")
            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)
            SplashScreen1.updateinfo.Text = ("Stars Datase Update (New Table,""Job_List"")")
            MsgBox("Stars Datase Update (New Table,""Job_List"")")
            rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
        End If
        cnn1.Close()
    End Sub
 
End Module

Open in new window

Toms Edison

You are trying to create a table with name "Update". Update is a keyword in most dbms. Try using the name within square bracketss "[Update]" or try with another name for the table

awolarczuk

ASKER

Dim dt As DataTable = cnn1.GetSchema("Tables")

getting the error on this part of the code mate but have changed the update to updatet

Toms Edison

Try this
http://davidhayden.com/blog/dave/archive/2006/10/01/GetListOfTablesInMicrosoftAccessUsingGetSchema.aspx

awolarczuk

ASKER

thanks mate but what i am looking at here i am a bit lost if you wouldnt mind making it a example i learn better that way

Toms Edison

The code snippet was in C# and it was using data adaptor factory to create classes

I have converted the code to vb.net and made it a function. I am facing some issue with my MS Access so unable to test it below is the code. Try it out

Private Function TableExist(ByVal TableName As String) As Boolean
Dim userTables As DataTable
Dim connection As OleDbConnection = New OleDbConnection

'c:\test\test.mdb
connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=f:\test\database1.mdb"

'We only want user tables, not system tables
Dim restrictions(4) As String
restrictions(3) = "Table"

connection.Open()

'Get list of user tables
userTables = connection.GetSchema("Tables", restrictions)

'Use this block or the one below. This should be more effective. Also check if the column name is "table_name" in the userTables
Dim results() As DataRow = userTables.Select("table_name='update'")
If (results.Length > 0) Then
TableExist = bTableExist
End If
'Use this block or the one above
Dim bTableExist As Boolean = False
For i = 0 To userTables.Rows.Count
If userTables.Rows(i)("table_name") = TableName Then
bTableExist = True
Exit For
End If
Next

TableExist = bTableExist
End Function

awolarczuk

ASKER

hey tech thanks mate

This is the code i am using i am hoping that you could help me fix this so it works if not mearge it with yours if you wouldn't mind i am having a little bit of troube understand your correctly, maybe i have been at this to long today but i just need to get this little part done and it all done my whole app

Thanks in advance mate

Module update
    Public Sub dbu()
        connect()
 
        Dim rs As New ADODB.Recordset
        Dim sql As String
 
        sql = "select * from MSysNavPaneGroups where [name] =  job_information "
        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
 
        If (rs.BOF Or rs.EOF) Then
            GoTo updatet
        End If
updatet:
        sql = "CREATE TABLE Job_Information (ID integer PRIMARY KEY ,Joblist char(255))"
        Debug.Print(sql)
        rs.Open(sql, cnn1, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockPessimistic)
        SplashScreen1.updateinfo.Text = ("Stars Datase Update (New Table,""Update"")")
        rs.Update()
        MsgBox("Stars Datase Update (New Table,""Update"")")
        cnn1.Close()
    End Sub
 
End Module

Open in new window

ASKER CERTIFIED SOLUTION

Toms Edison

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

awolarczuk

ASKER

Record(s) cannot be read; no read permission on 'MSysNavPaneGroups'.

Mate the above is the error i am facing wit this part any ideas

Toms Edison

Check if the userid used to connect to the database has got access to read data
For connection string check this out
http://www.connectionstrings.com/?carrier=access
Also check the account under which your application is running has got read permission on the file

awolarczuk

ASKER

i am using the normal sa log with no password what do i need to change it to to get ths working or is there another way

Toms Edison

So I am assuming that you have specified "sa" as user id and "" as password in your connection string. and "sa" has got "read" privilege to this database table MSysNavPaneGroups. Am I right?

awolarczuk

ASKER

it has got read for all the tables i have added but this is system table is there another way i need to do this

awolarczuk

ASKER

ok i have just read the comment i put maybe doesnt make a lot of sence

Ok

I am using the normal Sa with no password to access the Tables

when i try to read the system table from acess i get a read error

This connection is working all hte say through my code just doesnt work here

is there something esle i need to do for system tables