How do I check the table in other databases (IN clause instance)?
problem:
How to reference the table in other databases? (Query the external table) How to use the in clause in Jet SQL? Where is it used?
method one:
Can SELECT queries data on tables in other databases?
Select * from tbl_family in ibrica.mdb "[5556]"; square bracket [] is the connection string of other databases
Method Two:
Select * from [; Database = C: /Db2.mdb; PWD = 1]. Table 1 as a, Table 1 as bwhere a.id = B.ID
Please refer to:
Here is the help of the SELECT statement, please note that the in select statement command The Microsoft Jet database engine returns information from the database as a set of records. Syntax SELECT [PREDICATE] {* | table. * | [Table.] Field1 [As Alias] [, [Table.] Field2 [As Alias] [, ...]]} from tableexpression [, ...] [in External Database] [WHERE ...]] [GROUP BY ...]] [Having ...]] [ORDER BY ...]] [WITH OWNERACCESS OPTION] IN clause identifies the table in the external database, for example DBASE or Paradox database, or Microsoft Jet external database, the Microsoft Jet database engine can be connected to it. Syntax Identification Target Table: [Select | Insert] INTO DESTINATION IN {PATH | ["Path" "Type"] | ["" [Type; Database = PATH]} Identification Data Source Table: From TableExpression IN {PATH | [" Path "TYPE"] | ["" [type; data = path]} The SELECT statement containing the IN clause has several parts: Some describe the name of the Destination external table, insert the data inserted into the table. The name of the TableExpression table can be obtained from these tables. This variable may be a separate table name, a set of queries, or the entire path of Inner Join, Left Join, or Right Join. Path directory or file, and these directories or files contain Table. The name of the Type database type if the database is not a Microsoft Jet database (such as DBASE III, DBASE IV, PARADOX 3.x or Paradox 4.x), use this type to create a Table. Note You can only connect to an external database at a time, in some cases, the PATH parameter can reference the directory containing the database file. For example, when using DBASE, FoxPro or Paradox database table, the PATH parameter specifies the directory that contains .dbf or .db files. The name of the table file can be obtained by the Destination or TableExpression parameter. In order to specify a non-Microsoft Jet database, it can be attached after the name (;) after the name, and it is entered with single quotation marks ('') or ("") dual quotes. For example, the two writes of 'DBase IV;' or "DBase IV;". You can also specify an external database using the Database reserved word. For example, the following example specifies the same table: ... from table in "" [dbase iv; Database = C: / dbase / data / sales;]; ... from table in "c: / dbase / data / sales" "DBASE IV;" Note To improve performance and easy to use, use the link table instead of IN. IN clause example The following table shows how you use IN clauses to capture data for external databases. In each example, it is assumed that the client table is saved in the external database.
External Database SQL Statement Microsoft® Jet Database Select CustomeridFrom Customersin OtherDb.mdb Where Customerid Like "A *"; DBASE III or IV. Capture Data is replaced by a DBASE III table with "dbase III;". Select CustomeridFrom Customerin "C: / DBase / Data / Sales" "DBASE IV;" Where customerid Like "a *"; DBASE III or IV uses database syntax. Select customeridfrom customerin "" [dbase iv; database = c: / dbase / data / sales;] Where customerid like "a *"; paradox 3.x or 4.x. To capture data by a paradox version 3.x table Take "Paradox 3.x;" alternative "Paradox 4.x;". Select CustomeridFrom Customerin "C: / Paradox / Data / Sales" "" paradox 4.x; "WHERE CUSTOMERID LIKE" a * "; paradox 3.x or 4.x Using Database Syntax. Select CustomeridFrom Customerin" [Paradox 4.x Database = c: / paradox / data / sales;] WHERE CUSTOMERID LIKE "a *"; Microsoft Excel Worksheet Select Customerid, CompanyNameFrom [Customers $] in "C: /Documents/xldata.xls" "Excel 5.0;" WHERE CustomerID Like "a *" ORDER BY CustomerID; a named range in the worksheet SELECT CustomerID, CompanyNameFROM CustomersRangeIN "c: /documents/xldata.xls" "EXCEL 5.0;" WHERE CustomerID Like "a *" ORDER BY CustomerID; -------------------------------------------------- -------------------------------------------------- - About text files can be such as Select * from [text; fmt = Delimited; HDR = yes; Database = C: /;]. [APARM # txt]; About Query text files in the in clause How to write link strings, you Can refer to: http://support.microsoft.com/default.aspx? Scid = kb; EN-US; 234201 & product = ACC About Excel
INSERT INTO Table Select * from [Excel 5.0; HDR = YES; Database = C: / Test.xls]. [Sheet1 $]; Description: c: /test.xls is the Excel file name SHET1 is to import the work design to import actually You have to get the link string and a convenient way is to use the Link Wizard, let's hand down a file into your database, then do the following code:
Function displayLinkStringFromLinkTable () 'First Tools menu -> references cited Microsoft ADO Ext 2.X for DDL and Security.' Reference ADOX Dim strTableName strTableName = "001" '001 is the name of the linked table Dim cat As New ADOX.Catalog Dim objTable As ADOX.table Set cat.ActiveConnection = CurrentProject.Connection For Each objTable In cat.Tables If objTable.Name = strTableName Then Debug.Print objTable.Properties ( "Jet OLEDB: Link Provider String"). Name Debug.Print objTable. Properties ("Jet OLEDB: LINK Provider String"). Value end if next objtablend functionhttp://www.access911.net webmaster collection
