Results 1 to 6 of 6

Thread: Connection between VB.net and access database

  1. #1

    Thread Starter
    New Member
    Join Date
    Oct 2021
    Posts
    3

    Question Connection between VB.net and access database

    Dear All,

    For making the connection between vb.net (2017) to access database, I wrote the following code. But I received the following error code for this statement: "Da2.fill (Ds2, "SPAT") and I don't know what is wrong with that. Please be aware that my access table name is SPAT which is in the access database located in the project \bin\debug folder.

    I will be highly appreciative to receive your valuable guide.

    Regards

    Error Code is:
    System.Data.OleDb.OleDbException: 'The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.'


    What I have tried:

    Imports System.Data
    Imports System.Data.OleDb
    Public Class Form1
    Dim strcon2 As String = "provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & My.Application.Info.DirectoryPath & "\EI064-Letter.accdb"
    Dim con2 As OleDbConnection
    Dim stroledb As String
    Dim Da2 As OleDbDataAdapter
    Dim Ds2 As New DataSet
    Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    Ds2.Reset()
    con2 = New OleDbConnection(strcon2)
    con2.Open()
    stroledb = "SELECT ID, Letter_Number, Letter_Date, Letter_Subject, Letter_PDF_File, Letter_Reference, Letter_Attachment, Letter_Reply_Requirement, Replied_Letter, Remark, FROM SPAT"
    Da2 = New OleDbDataAdapter(stroledb, con2)
    Da2.Fill(Ds2, "SPAT")
    con2.Close()
    DataGridView1.DataBindings.Clear()
    DataGridView1.DataBindings.Add(New Binding("datasource", Ds2, "SPAT"))
    End Sub
    End Class

  2. #2
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    24,401

    Re: Connection between VB.net and access database

    You need to inspect this line..

    stroledb = "SELECT ID, Letter_Number, Letter_Date, Letter_Subject, Letter_PDF_File, Letter_Reference, Letter_Attachment, Letter_Reply_Requirement, Replied_Letter, Remark, FROM SPAT"

    Try this…

    stroledb = "SELECT [ID], Letter_Number, Letter_Date, Letter_Subject, Letter_PDF_File, Letter_Reference, Letter_Attachment, Letter_Reply_Requirement, Replied_Letter, [Remark] FROM SPAT"

  3. #3
    eXtreme Programmer .paul.'s Avatar
    Join Date
    May 2007
    Location
    Chelmsford UK
    Posts
    24,401

    Re: Connection between VB.net and access database

    But check for misspelled field names too

  4. #4

    Thread Starter
    New Member
    Join Date
    Oct 2021
    Posts
    3

    Thumbs up Re: Connection between VB.net and access database

    Quote Originally Posted by .paul. View Post
    You need to inspect this line..

    stroledb = "SELECT ID, Letter_Number, Letter_Date, Letter_Subject, Letter_PDF_File, Letter_Reference, Letter_Attachment, Letter_Reply_Requirement, Replied_Letter, Remark, FROM SPAT"

    Try this…

    stroledb = "SELECT [ID], Letter_Number, Letter_Date, Letter_Subject, Letter_PDF_File, Letter_Reference, Letter_Attachment, Letter_Reply_Requirement, Replied_Letter, [Remark] FROM SPAT"
    Dear Paul,

    My problem was solved with your perfect guide. many many thanks.

  5. #5
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,747

    Re: Connection between VB.net and access database

    I don't know whether Remark is a reserved word, but you had a comma after Remark, and that would certainly be incorrect. .Paul. removed that extra comma, then blocked remark. Frankly, I like the way you used compound names for almost all fields. That's a good practice for field names, since reserved words are always single words. Changing the name of the Remark column to something that is two words, as you have done for the other fields, would be a good idea.

    Also, for a long time there could be an issue if you used ID as a field. People tend to use ID as the primary key, so, as in your Select statement, it tends to come first. That never causes an issue with Access, and may not cause a problem with anything else, anymore, but for several versions of Excel, if you tried to open a text file that started with "ID", you'd get a really obscure error message. Therefore, if you had an Access table that used ID as the primary key, and you exported a select to a text file and tried to open it in Excel...it would fail. That got me to no longer use ID as a field name, though I believe that issue with Excel has been fixed for over a decade, now.
    My usual boring signature: Nothing

  6. #6

    Thread Starter
    New Member
    Join Date
    Oct 2021
    Posts
    3

    Re: Connection between VB.net and access database

    Dear Shaggy,

    So many thanks for your perfect and complete reply. I wish all the best for you.

    Regards

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width