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 sheet1 is the work name to import To export to the Excel form, you can use the following methods:
Function JetSQLExportExcel () Dim strsql As String strsql = "select * into [Excel 5.0; HDR = YES; DATABASE = c: /temp/111test.xls]. [Sheet1] from admin" CurrentProject.Connection.Execute strsqlEnd Function In fact, you To get a link string, there is also a convenient way to use the Link Wizard, let's link a file to your database, then do the following code: Function DisplayLinkStringFromLinkTable () "First to Tools Menu -> Reference NuMrosoft 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 objTableEnd Function
Take away from
http://access911.net/71fab21e17dc.htm
