Error in adodc datasource

• • Reply

  15 years ago

  by novavb6

  Barry Fensom

  Bundaberg QLD, Australia

  Joined 15 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
  
  
  
  
  

  Report abuse
• • Reply

  15 years ago

  by novavb6

  Barry Fensom

  Bundaberg QLD, Australia

  Joined 15 years ago

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

  Report abuse
• • Reply

  15 years ago

  by novavb6

  Barry Fensom

  Bundaberg QLD, Australia

  Joined 15 years ago

  Here I go, answering my own questions again.

  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
  
  
  
  
  

  Report abuse
• • Reply

  15 years ago

  by Trilarian

  Trilarian

  New Orleans, United States

  Joined 15 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.
  
  
  Report abuse