Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Location
    Houston, TX
    Posts
    2

    Post Unanswered: Error using "FROM Table IN.." to get data from external DB

    I am trying to use the "FROM table IN external database" syntax to fill a combo box on a form. I capture the path/filename to the external database using the common dialog file function and inset it into the SQL statement as shown below:

    cmbReports.RowSourceType = "Table/Query"
    cmbReports.RowSource = "SELECT Customers.CompanyName FROM Customers IN " & Me!strDbName & ";"

    However, it always returns a "Syntax error in FROM clause".

    Any thoughts?

    Thanks,

    Joe Rich

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    You may change your code to:

    '---------------------------------------------------------
    Dim strSQL as String

    strSQL = "SELECT CompanyName FROM Customers IN " & Me!strDbName & ";"

    msgbox strSQL

    cmbReports.RowSourceType = "Table/Query"
    cmbReports.RowSource = strSQL
    '---------------------------------------------------------

    and post strSQL here. Otherwise it's very difficult to sugest anything.

  3. #3
    Join Date
    Mar 2002
    Location
    Houston, TX
    Posts
    2

    Duhhhh...never mind...got it

    Turns out you must enclose the reference to the external database in single quotes as below...

    cmbReports.RowSourceType = "Table/Query"
    cmbReports.RowSource = "SELECT Customers.CompanyName FROM Customers IN '" & Me!strDbName & "';"

  4. #4
    Join Date
    Nov 2001
    Posts
    336
    Here is what MS Access Help says about In clause:


    IN Clause
    Identifies tables in any external database to which the Microsoft Jet database engine can connect, such as a dBASE or Paradox database or an external Microsoft® Jet database.

    Syntax
    To identify a destination table:

    [SELECT | INSERT] INTO destination IN
    {path | ["path" "type"] | ["" [type; DATABASE = path]]}

    To identify a source table:

    FROM tableexpression IN
    {path | ["path" "type"] | ["" [type; DATABASE = path]]}

    A SELECT statement containing an IN clause has these parts:

    Part Description
    destination The name of the external table into which data is inserted.
    tableexpression The name of the table or tables from which data is retrieved. This argument can be a single table name, a saved query, or a compound resulting from an INNER JOIN, LEFT JOIN, or RIGHT JOIN.
    path The full path for the directory or file containing table.
    type The name of the database type used to create table if a database is not a Microsoft Jet database (for example, dBASE III, dBASE IV, Paradox 3.x, or Paradox 4.x).


    Remarks
    You can use IN to connect to only one external database at a time.

    In some cases, the path argument refers to the directory containing the database files. For example, when working with dBASE, Microsoft FoxPro®, or Paradox database tables, the path argument specifies the directory containing .dbf or .db files. The table file name is derived from the destination or tableexpression argument.

    To specify a non-Microsoft Jet database, append a semicolon ( to the name, and enclose it in single (' ') or double (" ") quotation marks. For example, either 'dBASE IV;' or "dBASE IV;" is acceptable.

    You can also use the DATABASE reserved word to specify the external database. For example, the following lines specify the same table:

    ... FROM Table IN "" [dBASE IV; DATABASE=C:\DBASE\DATA\SALES;];

    ... FROM Table IN "C:\DBASE\DATA\SALES" "dBASE IV;"



    --------------------------------------------------------------------------------

    Notes

    For improved performance and ease of use, use a linked table instead of IN.

    You can also use the IN reserved word as a comparison operator in an expression. For more information, see the In operator.


    --------------------------------------------------------------------------------

    See Also
    FROM Clause (Microsoft Jet SQL) SELECT Statement (Microsoft Jet SQL)
    INNER JOIN Operation (Microsoft Jet SQL) SELECT...INTO Statement (Microsoft Jet SQL)
    INSERT INTO Statement (Microsoft Jet SQL) SQL Aggregate Functions (SQL)
    LEFT JOIN, RIGHT JOIN Operations (Microsoft Jet SQL)


    Example
    IN Clause Example


    IN Clause Example

    The following table shows how you can use the IN clause to retrieve data from an external database. In each example, assume the hypothetical Customers table is stored in an external database.

    External database SQL statement
    Microsoft® Jet database

    SELECT CustomerID
    FROM Customers
    IN OtherDB.mdb
    WHERE CustomerID Like "A*";

    dBASE III or IV.
    To retrieve data from a dBASE III table, substitute "dBASE III;" for "dBASE IV;".

    SELECT CustomerID
    FROM Customer
    IN "C:\DBASE\DATA\SALES" "dBASE IV;"
    WHERE CustomerID Like "A*";

    dBASE III or IV using Database syntax.

    SELECT CustomerID
    FROM Customer
    IN "" [dBASE IV; Database=C:\DBASE\DATA\SALES;]
    WHERE CustomerID Like "A*";

Posting Permissions

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