Error in adodc datasource

  • 11 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




  • 11 years ago

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

  • 11 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




  • 11 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.

“Measuring programming progress by lines of code is like measuring aircraft building progress by weight.” - Bill Gates