• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 261
  • Last Modified:

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

0
awolarczuk
Asked:
awolarczuk
  • 7
  • 6
1 Solution
 
TechTiger007Commented:
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
0
 
awolarczukAuthor Commented:
Dim dt As DataTable = cnn1.GetSchema("Tables")

getting the error on this part of the code mate but have changed the update to updatet
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
awolarczukAuthor Commented:
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
0
 
TechTiger007Commented:
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
0
 
awolarczukAuthor Commented:
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

0
 
TechTiger007Commented:
What is the issue you are facing with this part of code?
I find that your select query is missing quotes. Change it to
sql = "select * from MSysNavPaneGroups where [name] =  'job_information'"
0
 
awolarczukAuthor Commented:
Record(s) cannot be read; no read permission on 'MSysNavPaneGroups'.

Mate the above is the error i am facing wit this part any ideas
0
 
TechTiger007Commented:
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
0
 
awolarczukAuthor Commented:
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
0
 
TechTiger007Commented:
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?
0
 
awolarczukAuthor Commented:
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
0
 
awolarczukAuthor Commented:
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
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now