Error in adodc datasource

  • 13 years ago

    I am getting the following error when my table name contains spaces. (Maybe spaces in table names are not supported, but if that is the case, why can I create tables with spaces in their name?)

    AdodcInput (My Adodc control)

    The Microsoft Jet Database Engine cannot find the input table or query 'C1INtwo'

    it should actually be 'C1INtwo part'

    I have created a msgbox to test the string and the msgbox displays it properly.

    Anyway, here is code: Form name is frmNewProduct2, using vb6

    Private Sub Form_Load()
    Dim prodcode
    Label2.Caption = "Enter in your Input Requirements. Eg Length, Width etc."
    AdodcInputs.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\products.mdb"
    prodcode = frmNewProduct1.txtProductIDInput.Text
    catagory = frmNewProduct1.txtcatagory
    txtcat.Text = catagory





    MsgBox "SELECT InputName FROM " & catagory & "IN" & prodcode
    AdodcInputs.RecordSource = "SELECT InputName FROM " & catagory & "IN" & prodcode
    Set DGInputs.DataSource = AdodcInputs
    Unload frmNewProduct1
    Me.Tag = prodcode
    End Sub




  • 13 years ago

    okay, looks like spaces are not supported, using underscores instead.

  • 13 years ago

    Here I go, answering my own questions again. Wink [;)]

    I did some reading up on SQL, if the table name contains a space, You must use "[]" brackets around the table name.

    So here is my changed code:

    Dim prodcode
    Label2.Caption = "Enter in your Input Requirements. Eg Length, Width etc."
    AdodcInputs.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source=" & App.Path & "\products.mdb"


    prodcode = frmNewProduct1.txtProductIDInput.Text

    catagory = frmNewProduct1.
    txtcatagory

    txtcat.Text = catagory

    'Next line is changed, added square brackets around table name

    Source = "SELECT InputName FROM [" & catagory & "IN" & prodcode & "]"
    'MsgBox Source
    AdodcInputs.RecordSource = Source
    Set DGInputs.DataSource = AdodcInputs
    Unload frmNewProduct1
    Me.Tag = prodcode




  • 13 years ago
    I have made the same mistake above multiple times.  I would suggest always using [] for any table or field name.  While it is cumbersome at first, it is a good habit to get it in to - especially when your code grows and contains many sql statements.  So far(correct me if I'm wrong), I have not found a driver that will error if the [] was used, but have found some drivers that will error if you do not use [] even on a table with no spaces.

Post a reply

Enter your message below

Sign in or Join us (it's free).

Contribute

Why not write for us? Or you could submit an event or a user group in your area. Alternatively just tell us what you think!

Our tools

We've got automatic conversion tools to convert C# to VB.NET, VB.NET to C#. Also you can compress javascript and compress css and generate sql connection strings.

“Theory is when you know something, but it doesn't work. Practice is when something works, but you don't know why. Programmers combine theory and practice: Nothing works and they don't know why.”