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.
Hi Erik,
ReplyDeleteYou mention: "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."
I have a VB6 application working with SQLCE V3.5 and would like to use parameterized inserts but I'm finding that it crashes out of the application and the IDE when I call .execute. I can get it to run a parameterized insert with just one parameter.
Are parameterized inserts supported in SQLCE? Or when using the OLEDB provider with ADODB?
Current VB6 test code:
Private Sub TestParamQuery()
Dim conSdf As New ADODB.Connection
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Dim sql As String
mstrSqlCeConStr = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Test\exportTest.sdf;Persist Security Info=False;"
conSdf.Open mstrSqlCeConStr
Set cmd = New ADODB.Command
cmd.ActiveConnection = conSdf
cmd.CommandType = adCmdText
cmd.Prepared = True
sql = "insert into tblTest (Col1, Col2) values (@Col1, @Col2)"
Set param = cmd.CreateParameter("Col1", adInteger, adParamInput, , 1)
cmd.Parameters.Append param
Set param = cmd.CreateParameter("Col2", adInteger, adParamInput, , 2)
cmd.Parameters.Append param
cmd.CommandText = sql
cmd.Execute ' , , adExecuteNoRecords
conSdf.Close
Set conSdf = Nothing
Set cmd = Nothing
End Sub
Thanks very much,
Tim
Parameterized inserts, update and deletes are fully supported by the ADO.NET provider.
ReplyDeleteAdd error handling to your code to see what the errormessage and number is. I have not tested this, as I was expecting this to not work at all.
Thanks Erik. Unfortunately it crashes catastrophically and there's no chance to access the error. I'm rewriting in .net...
Delete