|
code
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
SQL Server Compact Edition with VB6Has anyone actually used SQL Server Compact Edition with VB6? I can't
seem to find much useful information. Does it work? Does it support the full subset of OLE DB functionality? I read somewhere that it doesn't even support the concept of a recordset. "Frank Rizzo" <n***@none.net> wrote in message I've tinkered with it some (actually, both v3.1 and v3.5). Both versions news:OGjWBERrJHA.4364@TK2MSFTNGP04.phx.gbl... > Has anyone actually used SQL Server Compact Edition with VB6? I can't > seem to find much useful information. Does it work? Does it support the > full subset of OLE DB functionality? I read somewhere that it doesn't > even support the concept of a recordset. will work with VB6 using ADO and the OLEDB provider. Each version has a different provider and you must use the correct one for the .sdf file you're using. IOW, you can't use the v3.1 provider with a v3.5 .sdf file and visa-versa. And no, OLEDB is not fully supported. For example, you cannot compact and repair the .sdf database file and for the life of me, I could not create a nvarchar data type (even though SSCE has that data type) in a table using ADOX. I don't know what you mean by not supporting the concept of a recordset. You can write a SELECT query and the resultset is in an ADO Recordset object. There are limitations, though. For example, SSCE does not support batches or multiple-step SQL statements. And the SQL statements and functions that are supported are rather limited. These, however, have nothing to do with VB6 or the OLEDB provider for SSCE. They are limitations of SSCE itself. For myself, when the app needs a local database storage file, I still prefer an Access .mdb file. -- Mike MikeD wrote:
Show quoteHide quote > Wow, that's good. I then have these questions:> "Frank Rizzo" <n***@none.net> wrote in message > news:OGjWBERrJHA.4364@TK2MSFTNGP04.phx.gbl... >> Has anyone actually used SQL Server Compact Edition with VB6? I can't >> seem to find much useful information. Does it work? Does it support >> the full subset of OLE DB functionality? I read somewhere that it >> doesn't even support the concept of a recordset. > > > I've tinkered with it some (actually, both v3.1 and v3.5). Both versions > will work with VB6 using ADO and the OLEDB provider. Each version has a > different provider and you must use the correct one for the .sdf file > you're using. IOW, you can't use the v3.1 provider with a v3.5 .sdf file > and visa-versa. > > And no, OLEDB is not fully supported. For example, you cannot compact > and repair the .sdf database file and for the life of me, I could not > create a nvarchar data type (even though SSCE has that data type) in a > table using ADOX. > > I don't know what you mean by not supporting the concept of a recordset. > You can write a SELECT query and the resultset is in an ADO Recordset > object. There are limitations, though. For example, SSCE does not > support batches or multiple-step SQL statements. And the SQL statements > and functions that are supported are rather limited. These, however, > have nothing to do with VB6 or the OLEDB provider for SSCE. They are > limitations of SSCE itself. > > For myself, when the app needs a local database storage file, I still > prefer an Access .mdb file. > 1. Where is the download for the OLEDB provider version 3.5? 2. Do they work on Windows 2000? 3. If they don't support multiple-step SQL statements, how does one get the latest identity field after an insert? Thanks. "Frank Rizzo" <n***@none.net> wrote in message news:ehrRxhVrJHA.3988@TK2MSFTNGP05.phx.gbl... There is no separate download for the provider. It's installed as part of the SSCE runtime installation. See below for a link to > > 1. Where is the download for the OLEDB provider version 3.5? various SSCE downloads > 2. Do they work on Windows 2000? v3.1 Yesv3.5 No (or at least not supported by MS which does not necessarily mean it won't work) > 3. If they don't support multiple-step SQL statements, how does one get the latest identity field after an insert? As far as I know, you have to execute a 2nd query. For exampleoConn.Execute "INSERT INTO tablename (Col1, Col2, Col3) VALUES (1,2,3)" oConn.Execute "SELECT @@IDENTITY AS IDENTITY_VALUE" Also keep in mind that SSCE does not support views, triggers, and stored procedures. If you want to learn more about SSCE, I suggest you download the Books Online and read as much as you want about it. http://www.microsoft.com/Sqlserver/2005/en/us/compact-downloads.aspx I also recommend you go here http://www.microsoft.com/sqlserver/2005/en/us/compact.aspx and click the "Choosing between SQL Server Compact 3.5 and SQL Server Express" link. This link is to a Word document that provides some information you may find helpful. For general questions you may still have about SSCE that don't pertain directly to VB6, a SQL Server newsgroup is probably a better place to ask. -- Mike On 03/25/2009 10:07, MikeD wrote:
> "Frank Rizzo"<n***@none.net> wrote in message news:ehrRxhVrJHA.3988@TK2MSFTNGP05.phx.gbl... I just installed and I don't see it. I opened the Data Link Properties, >> 1. Where is the download for the OLEDB provider version 3.5? > > There is no separate download for the provider. It's installed as part of the SSCE runtime installation. See below for a link to > various SSCE downloads Provider tab and I don't see the SQL Compact provider. Perhaps it's called something else? I am able to edit compact databases from SQL Server Management Studio, so I know it's installed. Also, I tried to get the top 10 records and it's saying I have a syntax error. How does one do this, if you know. Show quoteHide quote > >> 2. Do they work on Windows 2000? > > v3.1 Yes > v3.5 No (or at least not supported by MS which does not necessarily mean it won't work) > >> 3. If they don't support multiple-step SQL statements, how does one get the latest identity field after an insert? > > As far as I know, you have to execute a 2nd query. For example > > oConn.Execute "INSERT INTO tablename (Col1, Col2, Col3) VALUES (1,2,3)" > oConn.Execute "SELECT @@IDENTITY AS IDENTITY_VALUE" > > Also keep in mind that SSCE does not support views, triggers, and stored procedures. If you want to learn more about SSCE, I > suggest you download the Books Online and read as much as you want about it. > > http://www.microsoft.com/Sqlserver/2005/en/us/compact-downloads.aspx > > I also recommend you go here > > http://www.microsoft.com/sqlserver/2005/en/us/compact.aspx > > and click the "Choosing between SQL Server Compact 3.5 and SQL Server Express" link. This link is to a Word document that provides > some information you may find helpful. > > > For general questions you may still have about SSCE that don't pertain directly to VB6, a SQL Server newsgroup is probably a better > place to ask. > In SQL Management studio that ships with SQL 2008 Express (SQL compact
edition) you can right-click the database and select "Edit Top 200 Record." Then you can edit the SQL in "SQL Pane" to what ever number of top records you want. I don't remember the SQL 2005 Management Studio... but I guess it has similar feature. Show quoteHide quote "Frank Rizzo" <n***@none.net> wrote in message news:OBowq$arJHA.3700@TK2MSFTNGP06.phx.gbl... > On 03/25/2009 10:07, MikeD wrote: >> "Frank Rizzo"<n***@none.net> wrote in message >> news:ehrRxhVrJHA.3988@TK2MSFTNGP05.phx.gbl... >>> 1. Where is the download for the OLEDB provider version 3.5? >> >> There is no separate download for the provider. It's installed as part >> of the SSCE runtime installation. See below for a link to >> various SSCE downloads > > I just installed and I don't see it. I opened the Data Link Properties, > Provider tab and I don't see the SQL Compact provider. Perhaps it's > called something else? > > I am able to edit compact databases from SQL Server Management Studio, so > I know it's installed. > > Also, I tried to get the top 10 records and it's saying I have a syntax > error. How does one do this, if you know. > > >> >>> 2. Do they work on Windows 2000? >> >> v3.1 Yes >> v3.5 No (or at least not supported by MS which does not necessarily mean >> it won't work) >> >>> 3. If they don't support multiple-step SQL statements, how does one get >>> the latest identity field after an insert? >> >> As far as I know, you have to execute a 2nd query. For example >> >> oConn.Execute "INSERT INTO tablename (Col1, Col2, Col3) VALUES (1,2,3)" >> oConn.Execute "SELECT @@IDENTITY AS IDENTITY_VALUE" >> >> Also keep in mind that SSCE does not support views, triggers, and stored >> procedures. If you want to learn more about SSCE, I >> suggest you download the Books Online and read as much as you want about >> it. >> >> http://www.microsoft.com/Sqlserver/2005/en/us/compact-downloads.aspx >> >> I also recommend you go here >> >> http://www.microsoft.com/sqlserver/2005/en/us/compact.aspx >> >> and click the "Choosing between SQL Server Compact 3.5 and SQL Server >> Express" link. This link is to a Word document that provides >> some information you may find helpful. >> >> >> For general questions you may still have about SSCE that don't pertain >> directly to VB6, a SQL Server newsgroup is probably a better >> place to ask. >> > "Frank Rizzo" <n***@none.net> wrote in message news:OBowq$arJHA.3700@TK2MSFTNGP06.phx.gbl... Due to the nature of SSCE, the provider is not exposed though Data Link Properties.> On 03/25/2009 10:07, MikeD wrote: >> "Frank Rizzo"<n***@none.net> wrote in message news:ehrRxhVrJHA.3988@TK2MSFTNGP05.phx.gbl... >>> 1. Where is the download for the OLEDB provider version 3.5? >> >> There is no separate download for the provider. It's installed as part of the SSCE runtime installation. See below for a link >> to >> various SSCE downloads > > I just installed and I don't see it. I opened the Data Link Properties, Provider tab and I don't see the SQL Compact provider. > Perhaps it's called something else? > For v3.1 (or v3.0), the provider name is "Microsoft.SQLSERVER.MOBILE.OLEDB.3.0" For v3.5, the provider name is "Microsoft.SQLSERVER.CE.OLEDB.3.5" The only other thing you need to include in your connection string is the Data Source, and that's merely the path and filename to the .sdf file. IMO, this is the easiest way to open a connection: Dim oConn As ADODB.Connection Dim sFileName As String sFileName = "c:\blah\blah\mydb.sdf" Set oConn = New ADODB.Connection With oConn .Provider = "Microsoft.SQLSERVER.CE.OLEDB.3.5" .Open sFileName End With You can set CursorLocation and other properties if you want (before calling the Open method, of course). > As I recall, SSCE does not support SELECT TOP. This would be one of the things you need to consult SSCE Books Online about. If > Also, I tried to get the top 10 records and it's saying I have a syntax error. How does one do this, if you know. you've worked with the full SQL Server (or even the Express edition), you're going to find SSCE considerably more limited in its capabilities. -- Mike |
|||||||||||||||||||||||
Other interesting topics