DAO Database Collection FAQ

*DAO Database Collection FAQ*
1. How do I check in my application if DAO is installed? 2. What is the newest DAO version? 3. Why DAO 3.5 doesn't works on my Windows 95? 4. What DAO ISAM drivers are available on my computer? 5. Where can I find DAO informations? updated!!! 6. Is DAO free? 7. Is DAO available in IIS? 8. How do I create a new Access database? 9. How do I compact an Access database? 10. How do I repair a corrupted Access database? 11. How do I delete a table from the Acess database? 12. How do I delete all records from the table? 13. How do I find all available tables and queries at runtime? 14. How to avoid showing DAO exceptions? 15. What limitations has MS Access database? 16. How to use multiword table names? 17. How to filter date fields? 18. How to use DAO components in multithreaded application? 19. Why DAO components aren't available for Delphi 2 and C++ Builder 1? 20. How to specify index in DAODataSet component? 21. How to sort in descending order? 22. How to create multiline SQL statement at runtime? 23. Why I get error message "Can't start your application. The workgroup information file is missing or opened exclusively by another user."? 24. Why my CGI application with DAO components don't work? 25. How to set number of threads in DAO 3.5? 26. How to use DAO 3.5 functions in C++ Builder? 27. Why I can't link table from one mdb database to another? 28. Why I can't enter time without date into date/time field? 29. How to refresh DAO 3.5 cache? 30. How do I create an index? 31. Can I use MS Access 95 databases with DAO 3.5? 32. How do I import data from an external table? 33. Has DAO 3.5 support for record locking? 34. How can I link dBase table to MS Access database in my application? 35. How to create custom database property? 36. How can I change database password in my application? 37. How to specify MS Excell range of cells to access? 38. How to switch off header in MS Excell worksheet? 39. What limitations have text tables? 40. Is it possible to use separate directory for the database file and the lock file? 41. Can I use HTML Import and HTML Export directly from/to my website? 42. Supports DAO 3.5 multi-threading on Windows 95? 43. Which threading model supports DAO 3.5? 44. Can I use URL for specifying mdb database location? 45. How to use heterogenous queries on databases which are protected with password? 46. How do I get a list of all relationships in a database? 47. How do I get a list of all fields in a table? 48. How do I get a list of all users? 49. How do I get a list of all user groups? 50. How do I create or delete a new user or user group? 51. How do I access an mdb database on CD? 52. How do I export table to Excel worksheet? 53. How do I export table to comma delimited text file? 54. How do I get a list of users currently logged to database? 55. How do I change field name in my application? 56. How do I change field type in my application? 57. How do I create a relation in my application? 58. How do I create an autoincrement field in my application? 59. Why doesn't work my parameterized query? 60. How to use master-detail relationsip on queries with equal field names? 61. Is it possible to undelete deleted records in MS Access database? 62. How secure are password encrypted MS Access databases? 63. How to get database properties like Title, Author and Company? 64. How to improve DAO performance? 65. How to select records where some field begins with given character? I try to use SELECT * FROM MyTable WHERE Name = 'T%', but it don't works. 66. How to copy some table into another Access database? 67. How to retrieve DAO error code? 68. How to convert Access database into newer format? 69. How do I get a list of table indexes? 70. How to get the date/time the table was created? 71. How to get the date/time the table was last modified? 72. How to create a new table with Boolean field? 73. How to add a new Boolean field into existing table? 74. How do I use regional formatted date/time values? 75. How do I create 'True/False' formatted boolean field? 76. How do I retrieve Access field caption? 77. How do I use DAO for fast inserting records into Access table? 78. How do I create/delete/enumerate Access queries? 79. How do I refresh TableDefs collection? 80. How do I retrieve Access containers and documents? 81. How do I retrieve table permissions? 82. How do I change table permissions? 83. How do I retrieve records from external Access database? 84. How do I rename a table? 85. How do I set Unicode Compression for given field? 86. How do I change an existing user's password? 87. How do I set Required field attribute? 88. How do I create a new field with default value? 89. How do I create a new field with validation rule? 90. How do I set "Format" property to "Short Date" for a Date/Time data type field? 91. How do I update the connection information for a linked table? 1. How do I check in my application if DAO is installed? try ShowMessage('DAO ' + GetDBEngine(dvDAO35).Version + ' installed'); except ShowMessage('DAO 3.5 not installed'); end; try ShowMessage('DAO ' + GetDBEngine(dvDAO36).Version + ' installed'); except ShowMessage('DAO 3.6 not installed'); end; You can also use the DAOTEST.EXE utility (supplied with DAO components) to confirm that DAO is correctly installed. 2. What is the newest DAO version? Newest DAO version is 3.6. It is installed with Microsoft Office 2000. 3. Why DAO 3.5 doesn't works on my Windows 95? Copy OLEAUT32.DLL build 2.20.4054 or later to the SYSTEM directory and register it with REGSVR32.EXE. Define the TEMP environment variable and check if you have enough disk space. Check if EXPSRV.DLL library is installed, its required by VBAJET32.DLL. 4. What DAO ISAM drivers are available on my computer? Run REGEDIT.EXE program. List of Jet 3.5 ISAM drivers (used by DAO 3.5, DAO 3.51) is available under this registry key: MyComputer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\ISAM Formats ODBC; dBASE 5.0; dBASE III; dBASE IV; Excel 3.0; Excel 4.0; Excel 5.0; Excel 8.0; Exchange 4.0; FoxPro 2.0; FoxPro 2.5; FoxPro 2.6; FoxPro 3.0; FoxPro DBC; HTML Export; HTML Import; Jet 2.x; Lotus WK1; Lotus WK3; Lotus WK4; Paradox 3.x; Paradox 4.x; Paradox 5.x; Text; List of Jet 4.0 ISAM drivers (used by DAO 3.6) are located in registry database at MyComputer\HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\4.0\ISAM Formats ODBC; dBASE 5.0; dBASE III; dBASE IV; Excel 3.0; Excel 4.0; Excel 5.0; Excel 8.0; Exchange 4.0; HTML Export; HTML Import; Jet 2.x; Jet 3.x; Lotus WJ2; Lotus WJ3; Lotus WK1; Lotus WK3; Lotus WK4; Outlook 9.0; Paradox 3.x; Paradox 4.x; Paradox 5.x; Paradox 7.x; Text; 5. Where can I find DAO informations? DAO documentation: DAO35.HLP file in your DAO directory (C:\Program Files\Common Files\Microsoft Shared\DAO). Newsgroup: microsoft.public.vb.database.dao at msnews.microsoft.com Microsoft Access Tips for Serious Users Updated Version of Microsoft Jet 3.5 Available on MSL ACC97: CommitTrans Help Topic Uses dbFlushOSCacheWrites Constant HOWTO: Redistributing DAO with Your Visual C++ 5.0 Application HOWTO: How to Read .LDB Files PRB: Error "The Jet VBA File... Failed to Initialize When Called" PRB: The Jet VBA File VBAJet32.dll Failed to Initialize http://premium.microsoft.com/msdn/library/backgrnd/html/msdn_jetlock.htm HOW TO: Redistribute DAO 3.6 PRB: Jet 4.0 Row-Level Locking Is Not Available with DAO 3.60 HOWTO: Ensure Jet 3.5 Is Installed Correctly (Part I) 6. Is DAO free? No, ask Microsoft for DAO license informations. 7. Is DAO available in IIS? No, DAO is not available in IIS. Your provider must install it. 8. How do I create a new Access database? GetDBEngine.CreateDatabase('C:\database.mdb', dbLangGeneral, EmptyParam); 9. How do I compact an Access database? GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', EmptyParam, EmptyParam, EmptyParam); You can use additional parameter for specifying destination locale: GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', dbLangCzech, EmptyParam, EmptyParam); For data encryption/decryption use dbEncrypt/dbDecrypt constants: GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', '', dbEncrypt, EmptyParam); GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', '', dbDecrypt, EmptyParam); For database format conversion specify new database format: GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', '', dbVersion30, EmptyParam); dbVersion10 - Jet 1.0 format dbVersion11 - Jet 1.1 format dbVersion20 - Jet 2.0 format dbVersion30 - Jet 3.0 and 3.5 formats dbVersion40 - Jet 4.0 format For password protected database specify password as last parameter: GetDBEngine.CompactDatabase('C:\database.mdb', 'C:\newdb.mdb', EmptyParam, EmptyParam, ';pwd=MyPassword'); 10. How do I repair a corrupted database? GetDBEngine.RepairDatabase('C:\database.mdb'); 11. How do I delete a table from the Acess database? DAODatabase.DeleteTable('MyTable'); 12. How do I delete all records from the table? DAODatabase.EraseTable('MyTable'); 13. How do I find all available tables and queries at runtime? with DAODatabase.TableDefs do for i := 0 to Count - 1 do ShowMessage('Table: ' + Item[i].Name); with DAODatabase.QueryDefs do for i := 0 to Count - 1 do ShowMessage('Query: ' + Item[i].Name); 14. How to avoid showing DAO exceptions? procedure TForm1.DAODatasetDAOError(DataSet: TDataSet; E: EDatabaseError; var Action: TDataAction); begin Action := daAbort; end; 15. What limitations has MS Access database? MS Access 97: Max mdb file size: 1GB Max objects in database: 32768 Max modules in database: 1024 Max object name length: 64 Max password length: 14 Max user/group name length: 20 Max users connected to database: 255 Max record length (without Memos and BLOBs): 2048 Max fields in table: 255 Max nested transactions: 7 MS Access 2000: Max mdb file size: 2GB Max table size: 1GB Max objects in database: 32768 Max modules in database: 1000 Max object name length: 64 Max password length: 14 Max user/group name length: 20 Max users connected to database: 255 Max record length (without Memos and BLOBs): 2048 Max fields in table: 255 16. How to use multiword table names? Enclose multiword names into [ ] brackets: DAODataSet.SQL.Text := 'SELECT * FROM [Financial Reports]'; 17. How to filter date fields? Date values must be enclosed into ##: DAODataSet.Filter := 'MyDate = #10/14/00#'; DAODataSet.Filter := 'MyDate >= #10/14/00# and MyDate < #10/20/00#'; 18. How to use DAO components in multithreaded application? In each thread you need to call CoInitialize and CoUninitialize: uses ActiveX; procedure MyThread.Execute; begin CoInitialize(nil); try // your code finally CoUninitialize; end; end; 19. Why DAO components aren't available for Delphi 2 and C++ Builder 1? DAO components uses custom datasets, which aren't supported in Delphi 2 and C++ Builder 1 VCLs. 20. How to specify index in DAODataSet component? DAO components works like queries in MS Access (more similar to SQL databases): you needn't specify index, you only specify Sort property and Jet Engine themselves find and uses proper index. If suitable index doesn't exist, Jet Engine automatically sorts records, so you are not limited to sort only indexed fields. 21. How to sort in descending order? Simply add DESC clause after field name: DAODataSet.Sort := 'Name DESC'; 22. How to create multiline SQL statement at runtime? You need to disable parameter checking with ParamCheck property: with DAODataSet do begin ParamCheck := False; SQL.Clear; SQL.Add('SELECT Name'); SQL.Add('FROM MyTable'); ParamCheck := True; SQL.Add(''); // refreshing Params Open; end; 23. Why I get error message "Can't start your application. The workgroup information file is missing or opened exclusively by another user."? Don't use DAODatabase.Password property for entering password of encrypted database, but specify this password in DatabaseConnect property: DAODatabase.DatabaseConnect := ';pwd=Erik'; // database encrypted with password Erik Property DAODatabase.Password is determined for user's password, if user level security is enabled. These passwords are then stored in workgroup information file with extension .mdw. 24. Why my CGI application with DAO components don't works? You need to check, if you have properly installed DAO 3.5 on your web server: copy cgitest.exe, which is supplied with DAO components to your web script directory and try run it from your browser with http://localhost/scripts/cgitest.exe. If you use trial version of DAO components with IIS 4, you need also allow WWW service to interact with desktop (this enables showing DAO trial message): click Control Panel/Services/World Wide Web Publishing Service. Then click Startup and check "Allow Service to Interact With Desktop". 25. How to set number of threads in DAO 3.5? You need set value (default is 3) in registry database with regedit.exe: HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Jet\3.5\Engines\Jet 3.5\Threads This value determines number of threads for internal use in Jet engine only and not for using in application. 26. How to use DAO 3.5 functions in C++ Builder? C++ Builder convert DAO interface unit, which is originally created in Delphi, so return value of function in Delphi is converted as last parameter in C++ Builder function and C++ Builder functions always returns HRESULT type: In Delphi is CreateDatabase declared as: function CreateDatabase(const Name: WideString; const Locale: WideString, Option: OleVariant): Database; C++ Builder convert this function to: HRESULT CreateDatabase(const System::WideString Name, const System::WideString Locale, const System::OleVariant Option, _di_Database &CreateDatabase_result); You can use this function if you define and add last parameter: _di_Database db; GetDBEngine()->CreateDatabase("C:\\database.mdb", dbLangGeneral, EmptyParam, db); All DAO functions (DAO 3.5 interface) are declared in dao.hpp file. 27. Why I can't link table from one mdb database to another? It seems, that constant dbAttachedTable is improper defined in DAO, it should be zero: with DAODatabase.Handle do begin TableDef := CreateTableDef('MyLinkTable', 0 {dbAttachedTable}, 'COUNTRY', ';DATABASE=C:\db.mdb'); TableDefs.Append(TableDef); end; 28. Why I can't enter time without date into date/time field? Delphi requires to enter date in TDateTime fields. You should add events SetText and GetText to your TDateField field to enable entering and proper formatting of only time values: procedure TForm1.DAODataSet1TestTimeGetText(Sender: TField; var Text: String; DisplayText: Boolean); begin Text := TimeToStr(Sender.AsDateTime); end; procedure TForm1.DAODataSet1TestTimeSetText(Sender: TField; const Text: String); begin Sender.AsDateTime := StrToTime(Text); end; 29. How to refresh DAO 3.5 cache? GetDBEngine.Idle(dbRefreshCache); 30. How do I create an index? DAODatabase.ExecSQL('CREATE UNIQUE INDEX MyIndex ON MyTable (Name, Age)'); 31. Can I use MS Access 95 databases with DAO 3.5? Yes, MS Access 95 database format is compatible with DAO 3.5, so you can use MS Access 95 databases without any changes. 32. How do I import data from an external table? DAODatabase.ExecSQL('INSERT INTO DestTable SELECT * FROM SrcTable IN "" "dBase III;DATABASE=C:\"'); 33. Has DAO 3.5 support for record locking? No DAO 3.5 always locks 2kB page with records. 34. How can I link dBase table to MS Access database in my application? var TableDef: DAO.TableDef; with DAODatabase.Handle do begin TableDef := CreateTableDef('MyLinkedTable', EmptyParam, EmptyParam, EmptyParam); TableDef.Connect := 'dBase III;DATABASE=C:\'; TableDef.SourceTableName := 'industry.dbf'; TableDefs.Append(TableDef); end; 35. How to create custom database property? with DAODatabase.Handle do Properties.Append(CreateProperty('PropertyName', dbText, 'PropertyValue', False)); 36. How can I change database password in my application? DAODatabase.ChangePassword('OldPassword', 'NewPassword'); Note 1: DAODatabase must be open in Exclusive mode Note 2: To remove database password set as NewPassword empty string Note 3: To set a new database password when using DAO 3.6: DAODatabase.ChangePassword(#0, 'NewPassword'); 37. How to specify MS Excell range of cells to access? DAODataSet.TableName := 'COUNTRY$A2:C4'; 38. How to switch off header in MS Excell worksheet? DAODataSet.DatabaseConnect := 'Excel 8.0;HDR=NO'; 39. What limitations have text tables? Max fields in table: 255 Max size of field name: 64 Max field width: 32766 Max rows: 65000 40. Is it possible to use separate directory for the database file and the lock file? No. 41. Can I use HTML Import and HTML Export directly from/to my website? Yes, you need only specify your URL: DAODataset.DatabaseConnect := 'HTML Import'; DAODataset.DatabaseName := 'http://localhost/webpage.htm'; 42. Supports DAO 3.5 multi-threading on Windows 95? DAO 3.5 only supports the Unicode interfaces for multi-threading. Because Microsoft Windows 95 does not fully suport Unicode, DAO can not be used in multiple threads on Windows 95. 43. Which threading model supports DAO 3.5? DAO 3.5 supports OLE Apartment-threading model. 44. Can I use URL for specifying mdb database location? No. 45. How to use heterogenous queries on databases which are protected with password? DAODataSet.SQL.Text := 'SELECT * FROM Country IN "" ";DATABASE=C:\SIMPLE.MDB;PWD=MyPassword"'; DAODataSet.Open; 46. How do I get a list of all relationships in a database? with DAODatabase.Relations do for i := 0 to Count - 1 do begin ShowMessage('Relation name: ' + Item[i].Name); ShowMessage('Master table: ' + Item[i].Table); ShowMessage('Detail table: ' + Item[i].ForeignTable); ShowMessage('Fields: '); with Item[i].Fields do for j := 0 to Count - 1 do ShowMessage(Item[j].Name + ':' + Item[j].ForeignName); end; 47. How do I get a list of all fields in a table? with DAODataset.Recordset.Fields do for i := 0 to Count - 1 do ShowMessage(Item[i].Name); 48. How do I get a list of all users? with DAODatabase.Users do for i := 0 to Count - 1 do ShowMessage(Item[i].Name); 49. How do I get a list of all user groups? with DAODatabase.Groups do for i := 0 to Count - 1 do ShowMessage(Item[i].Name); 50. How do I create or delete a new user or user group? DAODatabase.CreateUser('NewUser', 'NewUserPID', 'NewUserPassword'); DAODatabase.DeleteUser('NewUser'); DAODatabase.CreateGroup('NewGroup', 'NewGroupPID'); DAODatabase.DeleteGroup('NewGroup'); DAODatabase.AddUserToGroup('NewUser', 'NewGroup'); DAODatabase.RemoveUserFromGroup('NewUser', 'NewGroup'); 51. How do I access an mdb database on CD? Set exclusive access, so lock (ldb) file will be not created: DAODatabase.Exclusive := True; DAODatabase.Open; 52. How do I export table to Excel worksheet? DAODataSet.SQL.Text := 'SELECT * INTO MySheet IN "" "Excel 8.0;DATABASE=C:\MyFile.xls" FROM MyTable'; DAODataSet.Open; 53. How do I export table to comma delimited text file? You can export table to text format with these statements: DAODataSet.SQL.Text := 'SELECT * INTO MyText#TXT IN "" "Text;DATABASE=C:\MyDir" FROM MyTable'; DAODataSet.Open; If export to comma delimited text file is needed, then you must change line in SCHEMA.INI file from Format=Delimited(;) to Format=Delimited(,) and make export again. 54. How do I get a list of users currently logged to database? MSLDBUSR.DLL library is available for retrieving this information from lock (LDB) file. Delphi Users demo shows you how to use this library. See also http://support.microsoft.com/support/kb/articles/q186/3/04.asp for more informations about Jet locking. 55. How do I change field name in my application? DAODatabase.TableDefs.Item['MyTable'].Fields.Item['OldFieldName'].Name := 'NewFieldName'; 56. How do I change field type in my application? This is not possible directly with DDL statements or DAO objects, but you need to add new field, copy data from old field to new field (for example with UPDATE statement) and then delete old field. 57. How do I create a relation in my application? var Relation: DAO.Relation; Field: DAO.Field; begin with DAODatabase.Handle do begin // create relation Relation := CreateRelation('MyRelation', 'MasterTable', 'DetailTable', 0); // add fields to relation (don't forget to set their ForeignName property) Field := Relation.CreateField('MasterID', dbInteger, 0); Field.ForeignName := 'DetailID'; Relation.Fields.Append(Field); // append created relation to collection of all relations Relations.Append(Relation); end; end; 58. How do I create an autoincrement field in my application? var DAOField: DAO.Field; DAOField := CreateField(Name, DAO.dbLong, EmptyParam); DAOField.Attributes := DAOField.Attributes or dbAutoIncrField; 59. Why doesn't work my parameterized query? Parameter names in SQL statements must not begin with ":" SELECT * FROM MyTable WHERE MyField = MyParameter 60. How to use master-detail relationsip on queries with equal field names? You must use PARAMETERS clause to declare parameters, so conflicting field names will be not used: PARAMETERS ID LONG; SELECT * FROM MyTable WHERE MasterID = ID 61. Is it possible to undelete deleted records in MS Access database? No. 62. How secure are password encrypted MS Access databases? MS Acess databases aren't secure. Both database encryption password and admin password can be easily retrieved. See http://support.microsoft.com/default.aspx?scid=%2fsupport%2faccess%2fcontent%2fsecfaq.asp for further informations. 63. How to get database properties like Title, Author and Company? with DAODatabase1.Containers.Item['Databases'].Documents.Item['SummaryInfo'].Properties do begin try ShowMessage('Title: ' + Item['Title'].Value); except end; try ShowMessage('Author: ' + Item['Author'].Value); except end; try ShowMessage('Company: ' + Item['Company'].Value); except end; try ShowMessage('Subject: ' + Item['Subject'].Value); except end; try ShowMessage('Manager: ' + Item['Manager'].Value); except end; end; 64. How to improve DAO performance? Use DML (Data Manipulation Language) SQL statements instead of loops. For example deleting records with SQL statement 'DELETE FROM MyTable' is much faster than deleting each record separately with TDAOTable.Delete statement in loop. Jet database engine contains very sophisticated cost-based query optimizer. You can activate function ShowPlan, which enables you to see created plan of execution for each query: Create key in registry database: MyComputer\HKEY_LOCAL_MACHINE\SOFTWARE\MICROSOFT\Jet\3.5\Engines\Debug Under this key create string value with name JETSHOWPLAN and data ON. You can later turn off this function by setting data to OFF. *65. How to select records where some field begins with given character? I try to use SELECT FROM MyTable WHERE Name = 'T%', but it don't works.** You need to use LIKE operator: SELECT * FROM MyTable WHERE Name LIKE 'T' 66. How to copy some table into another Access database?* Connect DAODataSet to first database (db1.mdb), then use simple SQL statement: SELECT * INTO MyDstTable IN "" ";DATABASE=db2.mdb" FROM MySrcTable If you need to append records into existing table located in second database you can use: INSERT INTO MyDstTable IN "" ";DATABASE=db2.mdb" SELECT * FROM MySrcTable 67. How to retrieve DAO error code? You can retrieve all DAO error informations with this code (for example placed in OnDAOError event handler): with GetDBEngine.Errors do for i := 0 to Count - 1 do with Item[i] do ShowMessage( 'Description: ' + Description + #13 + 'Number: ' + IntToStr(Number) + #13 + 'Source: ' + Source + #13 + 'HelpContext: ' + IntToStr(HelpContext) + #13 + 'HelpFile: ' + HelpFile); 68. How to convert Access database into newer format? Use CompactDatabase method, see Q9. How to compact database? 69. How do I get a list of table indexes? with DAODatabase.Handle.TableDefs.Item['MyTable'].Indexes do for i := 0 to Count - 1 do begin ShowMessage('Index: ' + Item[i].Name); with Item[i] do for j := 0 to Fields.Count - 1 do ShowMessage('Field: ' + Fields.Item[j].Name); end; 70. How to get the date/time the table was created? ShowMessage(DAODatabase.Handle.TableDefs.Item['MyTable'].DateCreated); 71. How to get the date/time the table was last modified? ShowMessage(DAODatabase.Handle.TableDefs.Item['MyTable'].LastUpdated); 72. How to create a new table with Boolean field? var TableDef: DAO.TableDef; with DAODatabase1.Handle do begin TableDef := CreateTableDef('MyTable', EmptyParam, EmptyParam, EmptyParam); with TableDef do Fields.Append(CreateField('MyBoolField', DAO.dbBoolean, EmptyParam)); TableDefs.Append(TableDef); end; 73. How to add a new Boolean field into existing table? var TableDef: DAO.TableDef; with DAODatabase1.Handle do begin TableDef := TableDefs.Item['MyTable']; with TableDef do Fields.Append(CreateField('MyBoolField', DAO.dbBoolean, EmptyParam)); end; 74. How do I use regional formatted date/time values? Use Access CDate function: DAODataSet.Filter := 'MyDate=CDate("30.10.1999 10:20:30")'; // Slovak regional settings 75. How do I create 'True/False' formatted boolean field? Set 'Format' property of this field to 'True/False' value: with DAODatabase.Handle.TableDefs.Item['MyTable'].Fields.Item['MyBooleanField'] do begin // try to remove old Format property try Properties.Delete('Format') except end; // create new Format property Properties.Append(CreateProperty('Format', dbText, 'True/False', EmptyParam)); end; 76. How do I retrieve Access field caption? using DAO 3.6: ShowMessage(DAODataSet.Recordset.Fields['MyField'].Properties['Caption'].Value); using DAO 3.5: ShowMessage(DAODataSet.Recordset.Fields.Item['MyField'].Properties.Item['Caption'].Value); 77. How do I use DAO for fast inserting records into Access table? Delphi code: var i: Integer; Field: DAO.Field; with DAODataSet.Recordset do try Parent.BeginTrans; Field := Fields[0]; for i := 1 to 1000 do begin AddNew; Field.Value := i; Update(dbUpdateRegular, False); end; Parent.CommitTrans(dbForceOSFlush); except Parent.Rollback; raise; end; C++ Builder code: Dao::Recordset Recordset = DAODataSet->Recordset; try* { Recordset->Parent->BeginTrans(); Variant Field = ((Variant)(IDispatch )Recordset->Fields).OlePropertyGet("Item", 0); for* (int i = 0; i < 1000; ++i) { Recordset->AddNew(); Field.OlePropertySet("Value", i); Recordset->Update(dbUpdateRegular, false); } Recordset->Parent->CommitTrans(dbForceOSFlush); } catch (Exception &exception) { Recordset->Parent->Rollback(); throw; } 78. How do I create/delete/enumerate Access queries? // create query DAODatabase.Handle.CreateQueryDef('MyQuery', 'SELECT * FROM MyTable'); // delete query DAODatabase.Handle.DeleteQueryDef('MyQuery'); // enumerate queries with DAODatabase.Handle do for i := 0 to QueryDefs.Count - 1 do ShowMessage(QueryDefs[i].Name + ': ' + QueryDefs[i].SQL); 79. How do I refresh TableDefs collection? DAODatabase.TableDefs.Refresh; 80. How do I retrieve Access containers and documents? with DAODatabase do for i := 0 to Containers.Count - 1 do with Containers[i] do for j := 0 to Documents.Count - 1 do ShowMessage('Container: ' + Containers[i].Name + ' Document: ' + Documents[j].Name); 81. How do I retrieve table permissions? var Permissions: Integer; Permissions := DAODatabase.Containers['Tables'].Documents['MyTable'].Permissions; if Permissions = dbSecNoAccess then ShowMessage('NoAccess'); if Permissions and dbSecFullAccess = dbSecFullAccess then ShowMessage('FullAccess'); if Permissions and dbSecDelete = dbSecDelete then ShowMessage('Delete'); if Permissions and dbSecReadSec = dbSecReadSec then ShowMessage('ReadSec'); if Permissions and dbSecWriteSec = dbSecWriteSec then ShowMessage('WriteSec'); if Permissions and dbSecDBCreate = dbSecDBCreate then ShowMessage('DBCreate'); if Permissions and dbSecDBOpen = dbSecDBOpen then ShowMessage('DBOpen'); if Permissions and dbSecDBExclusive = dbSecDBExclusive then ShowMessage('DBExclusive'); if Permissions and dbSecDBAdmin = dbSecDBAdmin then ShowMessage('DBAdmin'); if Permissions and dbSecCreate = dbSecCreate then ShowMessage('Create'); if Permissions and dbSecReadDef = dbSecReadDef then ShowMessage('ReadDef'); if Permissions and dbSecWriteDef = dbSecWriteDef then ShowMessage('WriteDef'); if Permissions and dbSecRetrieveData = dbSecRetrieveData then ShowMessage('RetrieveData'); if Permissions and dbSecInsertData = dbSecInsertData then ShowMessage('InsertData'); if Permissions and dbSecReplaceData = dbSecReplaceData then ShowMessage('ReplaceData'); if Permissions and dbSecDeleteData = dbSecDeleteData then ShowMessage('DeleteData'); Use the UserName property, if you need to retrieve permissions for specifieds user, resp. group: with DAODatabase.Containers['Tables'].Documents['MyTable'] do begin UserName := 'MyUserName'; MyPermissions := Permissions; end ; 82. How do I change table permissions? DAODatabase.Containers['Tables'].Documents['MyTable'].Permissions := dbSecFullAccess; Use the UserName property, if you need to change permissions for specified user, resp. group: with DAODatabase.Containers['Tables'].Documents['MyTable'] do begin UserName := 'MyUserName'; Permissions := dbSecFullAccess; end ; 83. How do I retrieve records from external Access database? SELECT * FROM MyTable IN "C:\MyDB.mdb" Inserting records into a new table SELECT * INTO DestTable IN "C:\DestDB.mdb" FROM SrcTable SELECT * INTO DestTable FROM SrcTable IN "C:\SrcDB.mdb" Inserting records into an existing table INSERT INTO DestTable IN "C:\DestDB.mdb" SELECT * FROM SrcTable INSERT INTO DestTable SELECT * FROM SrcTable IN "C:\SrcDB.mdb" 84. How do I rename a table? DAODatabase.TableDefs['OldTableName'].Name := 'NewTableName'; 85. How do I set Unicode Compression for given field? with DAODatabase.TableDefs['MyTable'].Fields['MyField'] do try Properties['UnicodeCompression'].Value := -1; // 0 switches off Unicode compression except Properties.Append(CreateProperty('UnicodeCompression', dbBoolean, -1, False)); // 0 switches off Unicode compr. end; 86. How do I change an existing user's password? DAODatabase.Users.Item['UserName'].NewPassword('OldPassword', 'NewPassword'); 87. How do I set Required field attribute? DAODatabase.TableDefs['MyTable'].Fields['MyField'].Required := True; 88. How do I create a new field with default value? var Field: DAO.Field; Field := TableDef.CreateField('Name', DAO.dbText, 24); Field.DefaultValue := 'Erik'; TableDef.Fields.Append(Field); 89. How do I create a new field with validation rule? var Field: DAO.Field; Field := TableDef.CreateField('Size', DAO.dbInteger, EmptyParam); Field.ValidationRule := '>= 10 AND <= 1000'; Field.ValidationText := 'Size must be between 10 and 1000'; TableDef.Fields.Append(Field); 90. How do I set "Format" property to "Short Date" for a Date/Time data type field? with DAODatabase.Handle.TableDefs.Item['MyTable'].Fields.Item['MyDateField'] do begin // try to remove old Format property try Properties.Delete('Format') except end; // create new Format property Properties.Append(CreateProperty('Format', dbText, 'Short Date', EmptyParam)); end; 91. How do I update the connection information for a linked table? with DAODatabase.Handle.TableDefs['MyLinkedTable'] do begin ShowMessage('Current connect: ' + Connect); // change connect Connect := 'Excel 5.0;HDR=YES;IMEX=2;DATABASE=' + GetCurrentDir + '\MyData.XLS'; // refresh link RefreshLink; ShowMessage('New connect: ' + Connect); end;
Last Updated Monday March 3, 2008 Copyright � 1999-2008 WINSOFT. All rights reserved.