MS Access: what changed

Revision 0 to 1. Removed lines have a minus sign; added lines have a plus sign.

Title: MS Access

New		Line
1	+	This page explains how to use a Microsoft Access database using [[ODBCConn\|ODBC]] and FPC's built-in SQLDB.
2	+
3	+	## MS Access specifics
4	+
5	+	### UsePrimaryKeyAsKey
6	+
7	+	In the TSQLQuery properties, you may need to set Set UsePrimaryKeyAsKey: False
8	+
9	+	### Get just-inserted autonumber primary key
10	+
11	+	After inserting data, Access 2000 and higher supports getting the just-inserted autonumber primary key using this query:
12	+
13	+	``` SQL
14	+	SELECT @@IDENTITY
15	+	```
16	+
17	+	### ODBC drivers
18	+
19	+	#### Windows
20	+
21	+	There are 2 different [[ODBCConn\|ODBC]] drivers for Microsoft Access:
22	+
23	+	- the old driver that can only use .mdb format files, driver name is "Microsoft Access Driver (*.mdb)". Included in many Windows versions (since Windows 2000 up to and including Vista?); downloadable for older Windows versions as part of the MDAC components or Jet components. 32 bit only.
24	+	- the new driver that can access both .mdb and .accdb files, driver name is "Microsoft Access Driver (.mdb, .accdb)". Downloadable as the "Microsoft Access Database Engine 2010 Redistributable"; available as 32 and 64 bit.
25	+
26	+	As usual, when using 32 bit Lazarus/FPC, use 32 bit ODBC drivers. When using 64 bit Lazarus/FPC, use 64 bit ODBC drivers.
27	+
28	+	#### Unix/Linux
29	+
30	+	The [mdbtools project](https://github.com/mdbtools/mdbtools) offers limited support for MS Accesss. It includes an ODBC driver which might be used. Try installing packages like mdbtools mdbtools-dev mdbtools-gmdb
31	+
32	+	At least on Debian, the ODBC driver name is "MDBTools".
33	+
34	+	### Wildcards in SQL queries
35	+
36	+	Microsoft Access has a different wildcard syntax in SQL queries than other database systems. The following information is taken from a document "Intermediate Microsoft Jet SQL for Access 2000" at [MSDN](http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp), but is no longer found at this place (some copies still exist in the web at third-party sites). Tested to be valid.
37	+
38	+	\| Wildcard Character \| Description \|
39	+	\|----\|----\|
40	+	\| % (percent sign) \| Matches any number of characters and can be used anywhere in the pattern string. \|
41	+	\| _ (underscore) \| Matches any single character and can be used anywhere in the pattern string. \|
42	+	\| \[\] (square brackets) \| Matches any single character within the list that is enclosed within brackets, and can be used anywhere in the pattern string. \|
43	+	\| ! (exclamation point) \| Matches any single character not in the list that is enclosed within the square brackets. \|
44	+	\| \- (hyphen) \| Matches any one of a range of characters that is enclosed within the square brackets. \|
45	+
46	+	Note that
47	+
48	+	- the `WHERE` condition in Access must be put in round parenthesis
49	+	- strings must be single-quoted
50	+	- dates must be formatted like `#yyyy-mm-dd#` (in year-month-day order and enclosed by '#')
51	+	- field and table names with normally forbidden characters must be enclosed in square brackets.
52	+
53	+	Example: `SELECT * FROM [Birthday Table] WHERE ([Birth date] >= #2000-01-01#)`
54	+
55	+	## File-based DSN Instructions
56	+
57	+	A file DSN is simply where the connection settings are written to a file. The reason for having a file DSN is if you want to distribute a data source connection to multiple users on different systems without having to configure a DSN for each system. For instance, I can create a file DSN to a reporting database on my desktop. I can then send the file to my users. My users can save the file DSN to their hard drives and then point their reporting applications at the file DSN.
58	+
59	+	If you want to use a file-based DSN with the SQLDB ODBC driver:
60	+
61	+	### Set up the File DSN
62	+
63	+	- Go to your \[Data sources (ODBC)\] at the control panel administrative tools.
64	+	- Go to \[File DSN\] tab menu, Click Add, then Select \<microsoft access driver\>.
65	+	- Next, browse to your current Lazarus project path to save your .DSN file, because that dsn file will contain the configuration needed to access your database file (.mdb).
66	+	- Click Next, then Finish (you now have created a new .dsn file) of which will be use in your TODBCConnection \[FileDSN\].
67	+
68	+	#### File DSN contents
69	+
70	+	For reference, a file DSN that refers to an MS Access database may look something like this:
71	+
72	+	```DOS
73	+	[ODBC]
74	+	DRIVER=Microsoft Access Driver (*.mdb)
75	+	UID=admin
76	+	UserCommitSync=Yes
77	+	Threads=3
78	+	SafeTransactions=0
79	+	PageTimeout=5
80	+	MaxScanRows=8
81	+	MaxBufferSize=2048
82	+	FIL=MS Access
83	+	DriverId=25
84	+	DefaultDir=
85	+	DBQ=YOUR_msaccess.mdb
86	+	```
87	+
88	+	### Configure your project to use file-based DSN
89	+
90	+	Usage of grids and other data bound controls is the same as for any other SQLDB connector, so that is not covered here.
91	+
92	+	As you're using an ODBC connection, you should have a TODBCConnection object.
93	+
94	+	TOBDCConnection Properties:
95	+
96	+	FileDSN: the path+filename to the DSN file saved earlier, e.g. c:\mylazarus\project1\myFile.dsn Username: admin (or whatever username you need if you are using Access security) this parameter may not be needed if no MS Access security is used Do not put any other entries in the properties.
97	+
98	+	## System/User DSN
99	+
100	+	As explained in the [[ODBCConn\|ODBC]] article, you can also use system or user DSNs, where connection settings are defined in the ODBC control panel instead of stored in a file.
101	+
102	+	The TODBCConnection should have DatabaseName: \<name_of_your_DSN\>
103	+
104	+	## DSN-less connection
105	+
106	+	As explained in the [[ODBCConn\|ODBC]] article, you can also create a DSN-less connection to your Access database where you can specify all connection parameters in code, something like:
107	+
108	+	``` pascal
109	+	//Notice we're using the new MS Access driver, we could probably use the old one too:
110	+	conn.Driver:='Microsoft Access Driver (.mdb, .accdb)';
111	+	conn.Params.Add('DBQ=c:\somedirectory\test.mdb');
112	+	... add whatever parameters you want/need...
113	+	```
114	+
115	+	Here is an example how to query a table in an Access mdb file using TODBCConnection, TSQLTransaction and TSQLQuery; the mdb file exists in the same folder as the project binary:
116	+
117	+	``` pascal
118	+	procedure TForm1.Button1Click(Sender: TObject);
119	+	begin
120	+	//connection
121	+	ODBCConnection1.Driver := 'Microsoft Access Driver (.mdb, .accdb)';
122	+	ODBCConnection1.Params.Add('DBQ='+Application.Location+'test.mdb'); // path to mdb file required
123	+	ODBCConnection1.Params.Add('Locale Identifier=1031');
124	+	ODBCConnection1.Params.Add('ExtendedAnsiSQL=1');
125	+	ODBCConnection1.Params.Add('CHARSET=ansi');
126	+	ODBCConnection1.Connected := True;
127	+	ODBCConnection1.KeepConnection := True;
128	+
129	+	//transaction
130	+	SQLTransaction1.DataBase := ODBCConnection1;
131	+	SQLTransaction1.Action := caCommit;
132	+	SQLTransaction1.Active := True;
133	+
134	+	SQLQuery1.DataBase := ODBCConnection1;
135	+	SQLQuery1.UsePrimaryKeyAsKey := False;
136	+	SQLQuery1.SQL.Text := 'select * from Customers';
137	+	SQLQuery1.Open;
138	+	end;
139	+	```
140	+
141	+	## Example code
142	+
143	+	### Lazarus: data bound controls on forms
144	+
145	+	Working Source code sample:(Updated2_with_add_delete_update) <http://www.mediafire.com/file/ne1jx3zpnwzefq3/msaccesstest2.zip> \[Dead link - November 2020\]
146	+
147	+	``` pascal
148	+	unit Unit1;
149	+
150	+	{$mode objfpc}{$H+}
151	+
152	+	interface
153	+
154	+	uses
155	+	Classes, SysUtils, odbcconn, sqldb, db, FileUtil, LResources, Forms, Controls,
156	+	Graphics, Dialogs, DBGrids, DbCtrls, StdCtrls, Printers, PrintersDlgs;
157	+
158	+	type
159	+
160	+	{ TForm1 }
161	+
162	+	TForm1 = class(TForm)
163	+	Button1: TButton;
164	+	Button2: TButton;
165	+	Button3: TButton;
166	+	Button4: TButton;
167	+	Datasource1: TDatasource;
168	+	DBEdit1: TDBEdit;
169	+	DBEdit2: TDBEdit;
170	+	DBGrid1: TDBGrid;
171	+	ODBCConnection1: TODBCConnection;
172	+	PrintDialog1: TPrintDialog;
173	+	SQLQuery1: TSQLQuery;
174	+	SQLTransaction1: TSQLTransaction;
175	+	procedure Button1Click(Sender: TObject);
176	+	procedure Button2Click(Sender: TObject);
177	+	procedure Button3Click(Sender: TObject);
178	+	procedure Button4Click(Sender: TObject);
179	+	procedure FormCloseQuery(Sender: TObject; var CanClose: boolean);
180	+	procedure FormShow(Sender: TObject);
181	+	private
182	+	{ private declarations }
183	+	procedure PrintDbGrid(dbGrid:TdbGrid);
184	+	public
185	+	{ public declarations }
186	+	end;
187	+
188	+	var
189	+	Form1: TForm1;
190	+
191	+	implementation
192	+
193	+	{ TForm1 }
194	+
195	+	procedure TForm1.FormShow(Sender: TObject);
196	+	begin
197	+	//connection
198	+	ODBCConnection1.FileDSN := ExtractFilePath(Application.ExeName) + 'file.dsn';
199	+	ODBCConnection1.Connected := True;
200	+	ODBCConnection1.KeepConnection := True;
201	+
202	+	//transaction
203	+	SQLTransaction1.DataBase := ODBCConnection1;
204	+	SQLTransaction1.Action := caCommit;
205	+	SQLTransaction1.Active := True; //not applied to ms access(false)
206	+
207	+	SQLQuery1.DataBase := ODBCConnection1;
208	+	SQLQuery1.UsePrimaryKeyAsKey := False;
209	+
210	+	SQLQuery1.SQL.Text := 'select * from table1';
211	+
212	+	// :id is the parameter for the field name id
213	+	SQLQuery1.deleteSQL.Text := 'delete from table1 where id=:id';
214	+	//:name is the parameter for the field name name
215	+	SQLQuery1.updateSQL.Text := 'update table1 set name=:name where id=:id';
216	+
217	+	DataSource1.DataSet := SQLQuery1;
218	+	DBGrid1.DataSource := DataSource1;
219	+	DBGrid1.ReadOnly := true;
220	+
221	+	DBEdit1.DataField := 'id';
222	+	DBEdit1.DataSource := DataSource1;
223	+	DBEdit2.DataField := 'name';
224	+	DBEdit2.DataSource := DataSource1;
225	+
226	+	SQLQuery1.Open;
227	+	end;
228	+
229	+	function MulDiv(nNumber, nNumerator, nDenominator: Integer): Integer;
230	+	begin
231	+	Result:=Round(int64(nNumber)*int64(nNumerator)/nDenominator);
232	+	end;
233	+	procedure TForm1.PrintDbGrid(dbGrid:TdbGrid);
234	+	const
235	+	LeftMargin = 0.05;
236	+	TopMargin = 0.05;
237	+	BottomMargin = 0.05;
238	+	var
239	+	i: integer;
240	+	x,y: integer;
241	+	begin
242	+	if PrintDialog1.Execute then
243	+	begin
244	+	Printer.BeginDoc;
245	+	Printer.Canvas.Font.Size := 12;
246	+
247	+	y := Round(TopMargin*Printer.PageHeight);
248	+	dbGrid.DataSource.DataSet.First;
249	+	while not dbGrid.DataSource.DataSet.Eof do
250	+	begin
251	+	x := Round(LeftMargin*Printer.PageWidth);
252	+	for i := 0 to dbGrid.DataSource.DataSet.FieldCount-1 do
253	+	begin
254	+	printer.Canvas.TextOut(x,y,dbGrid.DataSource.DataSet.Fields[i].AsString);
255	+	x := x + MulDiv(dbGrid.Columns[i].Width,72, dbGrid.Width);
256	+	end;
257	+	dbGrid.DataSource.DataSet.Next;
258	+	y := y + printer.Canvas.TextHeight('A');
259	+	if y > (1-TopMargin-BottomMargin)* Printer.PageHeight then
260	+	begin
261	+	y := Round(TopMargin*Printer.PageHeight);
262	+	Printer.NewPage;
263	+	end;
264	+	end;
265	+	Printer.EndDoc;
266	+	end
267	+	else
268	+	Form1.caption := 'NO PRINTER INSTALLED';
269	+	end;
270	+
271	+	procedure TForm1.Button1Click(Sender: TObject);
272	+	begin
273	+	PrintDbGrid(DBGrid1);
274	+	end;
275	+
276	+	procedure TForm1.Button2Click(Sender: TObject);
277	+	begin
278	+	if button2.Caption = 'new' then
279	+	begin
280	+	SQLQuery1.Insert;
281	+	button2.Caption := 'save';
282	+	exit
283	+	end
284	+	else
285	+	begin
286	+	if (dbedit1.Text = '') or (dbedit2.Text = '')
287	+	then
288	+	begin
289	+	SQLQuery1.Cancel;
290	+	end
291	+	else
292	+	begin
293	+	if SQLQuery1.State = dsInsert then
294	+	begin
295	+	SQLQuery1.Post;
296	+	SQLQuery1.ApplyUpdates;
297	+	Form1.caption := 'ADDED';
298	+	end;
299	+	end;
300	+	end;
301	+	button2.Caption := 'new';
302	+	end;
303	+
304	+	procedure TForm1.Button3Click(Sender: TObject);
305	+	begin
306	+	if SQLQuery1.RecordCount>0 then
307	+	begin
308	+	SQLQuery1.Delete;
309	+	SQLQuery1.ApplyUpdates;
310	+
311	+	//REMOVED or ADD this 2 lines if there is a problem in your SQLdB
312	+	//THESE 2 lines apparently work around a bug in SQLdB for MS_Access dataBase'
313	+	//**** missing reference to bugtracker item; please create a bugtracker item
314	+	//SQLQuery1.Close;
315	+	//SQLQuery1.Open;
316	+
317	+	Form1.caption := 'DELETED';
318	+	end;
319	+	end;
320	+
321	+	procedure TForm1.Button4Click(Sender: TObject);
322	+	begin
323	+	if SQLQuery1.RecordCount>0 then
324	+	begin
325	+	SQLQuery1.Edit;
326	+	SQLQuery1.Post;
327	+	Sqlquery1.ApplyUpdates;
328	+	Form1.caption := 'UPDATED';
329	+	end;
330	+	end;
331	+
332	+	procedure TForm1.FormCloseQuery(Sender: TObject; var CanClose: boolean);
333	+	begin
334	+	SQLQuery1.Close;
335	+	CanClose := True;
336	+	end;
337	+
338	+	initialization
339	+	{$I unit1.lrs}
340	+
341	+	end.
342	+	```
343	+
344	+	### Create a database programmatically
345	+
346	+	Using either ODBC driver, you can programmatically create Microsoft Access databases. The following code is valid for both 32-bit and 64-bit applications on Windows.
347	+
348	+	Example code:
349	+
350	+	``` pascal
351	+	uses
352	+	..., sysutils, odbcsqldyn;
353	+
354	+	const
355	+	ODBC_ADD_DSN=1;
356	+	{ just for reference...
357	+	ODBC_CONFIG_DSN=2;
358	+	ODBC_REMOVE_DSN=3;
359	+	ODBC_ADD_SYS_DSN=4;
360	+	ODBC_CONFIG_SYS_DSN=5;
361	+	ODBC_REMOVE_SYS_DSN=6;
362	+	ODBC_REMOVE_DEFAULT_DSN=7; }
363	+
364	+	function SQLConfigDataSource(hwndParent: Integer; fRequest: Integer;
365	+	lpszDriverString: PChar; lpszAttributes: PChar): Integer; stdcall;
366	+	external 'odbccp32.dll';
367	+
368	+	function SQLInstallerError(iError: integer; pfErrorCode: PInteger;
369	+	lpszErrorMsg: String; cbErrorMsgMax: integer; pcbErrorMsg: PInteger): integer; stdcall;
370	+	external 'odbccp32.dll';
371	+
372	+	procedure CreateAccessDatabase(DatabaseFile: string);
373	+	var
374	+	cmd: String;
375	+	driver: String;
376	+	errCode, errMsgLength: DWord;
377	+	errMsg: String;
378	+	retCode: integer;
379	+	ext: String;
380	+	begin
381	+	ext := Lowercase(ExtractFileExt(DatabaseFile));
382	+
383	+	driver := 'Microsoft Access Driver (.mdb, .accdb)';
384	+	{ With this driver,
385	+	CREATE_DBV12 will create an .accdb format database;
386	+	CREATE_DBV4 will create an .mdb format database
387	+	http://stackoverflow.com/questions/9205633/how-do-i-specify-the-odbc-access-driver-format-when-creating-the-database
388	+	}
389	+	if ext = '.mdb' then
390	+	cmd := 'CREATE_DBV4="' + DatabaseFile + '"'
391	+	else if ext = '.accdb' then
392	+	cmd := 'CREATE_DBV12="' + DatabaseFile + '"'
393	+	else
394	+	raise Exception.Create('Invalid database file extension.');
395	+
396	+	retCode := SQLConfigDataSource(0, ODBC_ADD_DSN, PChar(driver), PChar(cmd));
397	+	if not (retCode in [SQL_SUCCESS, SQL_SUCCESS_WITH_INFO]) then
398	+	begin
399	+	//try alternate driver
400	+	driver := 'Microsoft Access Driver (*.mdb)';
401	+	cmd := 'CREATE_DB="' + DatabaseFile + '"';
402	+	retCode := SQLConfigDataSource(0, ODBC_ADD_DSN, PChar(driver), PChar(cmd));
403	+	end;
404	+
405	+	if not (retCode in [SQL_SUCCESS, SQL_SUCCESS_WITH_INFO]) then
406	+	begin
407	+	errCode := 0;
408	+	errMsgLength := 0;
409	+	SetLength(errMsg, SQL_MAX_MESSAGE_LENGTH);
410	+	SQLInstallerError(1, @errCode, PChar(errMsg), Length(errMsg), @errMsgLength);
411	+	SetLength(errMsg, errMsgLength);
412	+	raise Exception.CreateFmt('Error creating Access database: %s', [errMsg]);
413	+	end;
414	+	end;
415	+	```
416	+
417	+	### Character set issues
418	+
419	+	Old Access databases are encoded using an ANSI character set (I don't know about new ones - they probably use wide strings). Therefore, non-ASCII characters are not displayed correctly in a DBGrid or other data-aware controls. This can be fixed by attaching handlers to the OnGetText and OnSetText events of the dataset after opening:
420	+
421	+	``` pascal
422	+	uses
423	+	lconvencoding;
424	+
425	+	procedure TForm1.SQLQuery1AfterOpen(DataSet: TDataSet);
426	+	var
427	+	i: Integer;
428	+	begin
429	+	for i:=0 to DataSet.Fields.Count-1 do
430	+	begin
431	+	if DataSet.Fields[i].DataType=ftString then
432	+	begin
433	+	DataSet.Fields[i].OnGetText := @ConvertFromDB;
434	+	DataSet.Fields[i].OnSetText := @ConvertToDB;
435	+	end;
436	+	end;
437	+	end;
438	+
439	+	procedure TForm1.ConvertFromDB(Sender: TField; var aText: string; DisplayText: Boolean);
440	+	begin
441	+	if not Sender.IsNull then
442	+	aText := WinCPToUTF8(Sender.AsString); // if encoded in windows default code page
443	+	end;
444	+
445	+	procedure TForm1.ConvertToDB(Sender: TField; const aText: string);
446	+	begin
447	+	if aText <> '' then
448	+	Sender.Value := UTF8ToWinCP(aText);
449	+	end;
450	+	```
451	+
452	+	## Example database
453	+
454	+	Microsoft Access ships with the Northwind example database.
455	+
456	+	If you only have the Access Runtime installed, you can use this alternative database from the Mondial project as a test database: [1](http://databases.about.com/od/sampleaccessdatabases/a/Microsoft-Access-Sample-Database-Countries-Cities-And-Provinces.htm) \[Dead link - November 2020\]
457	+
458	+	## See also
459	+
460	+	- [[ODBCConn]] The ODBC connector this article uses
461	+
462	+	---
463	+	From the Free Pascal wiki, page "MS Access", as it stood on 2024-05-15 after a change by Wp, out of the wiki's dump of 2024-05-26. Copyright stays with the author who wrote it, according to the old wiki's [[Licensing]] page. If you have concerns about this material, [message the admin](/wiki-contact).

Back to history