This may sound a bit old-school, and it probably is, but some developers of Visual Basic still find that SQL Server Compact is a compelling local database solution.
To use SQL Server Compact with VB, you must have the SQL Server Compact runtime MSI installed on the computer, as the only way to access SQL Server Compact from VB is via the OLEDB provider, that must be registered on the machine by the MSI installer.
To use the OLEDB provider, you need the provider name, and for SQL Server Compact, this is:
Version 3.0/3.1: Microsoft.SQLSERVER.MOBILE.OLEDB.3.0
Version 3.5: Microsoft.SQLSERVER.CE.OLEDB.3.5
Version 4.0: Microsoft.SQLSERVER.CE.OLEDB.4.0
It my blog post here I describe how you can access and iterate a table from VBA.
XL Dennis has also blogged about the same subject.
Notice that you cannot access columns of type “image” using the OLEDB provider, let alone INSERT into these columns – I describe a workaround for the INSERT case in my blog post here.
As the database access from takes place via the OLEDB provider, for some connection string properties, make sure to use the ssce: variant, if available. So for example to access a password protected database file from VB/VBA, use this connection string format:
Data Source=mydb.sdf;ssce:database password=123
XL Dennis has also blogged about creating a SQL Server Compact database from VB/VBA.
In order to do UPDATE, INSERT and DELETE, you must construct the required SQL statements as strings, and simply execute them, using ADO.Connection .Execute method.
Finally, in my blog post here, I demonstrate that it is possible to use SQL Server Compact 4.0 from Classic ASP.
Hopefully, this collection of tips and tricks is enough to get you started with VBA/VB and SQL Server Compact.
