locked
Visual Basic 6.0 RRS feed

  • Question

  • Greetings,

    Firstly, apologies if this topic is out of the scope of this board, but I am stumped.

    Basically, we have an old Visual Basic 6.0 application which currently accesses an Access database, but which we would like to upgrade to an SQL compact edition database.  I have created the database, but have so far turned up next to no details reagarding how to connect to the damn thing in VB6.

    Can anyone help?  Or at least point me in the right direction?

    Many thanks,

    MadSkunk

    Thursday, March 22, 2007 12:42 PM

Answers

  • You are certainly not out of scope - here is the answer - some good old VB code:

    Dim pConn As ADODB.Connection

        Dim pRS As ADODB.Recordset

        Dim sStr As String

     

        Set pConn = New ADODB.Connection

        Set pRS = New ADODB.Recordset

      

       pConn.ConnectionString= "PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\\MyDB.sdf"

       

        pConn.Open

     

        sStr = "Select * from employee"

     

        ' Open the recordset

        pRS.Open sStr, pConn, adOpenForwardOnly

      

       Do Until pRS.EOF

     

         ' Employee table has an emp_fname column that represents the first name of employee

         ' List1 is a list control

         List1.AddItem pRS!emp_fname

         pRS.MoveNext

     

       Loop

     

       pRS.Close

       pConn.Close

     

     Set pRS = Nothing

     Set pConn = Nothing

    More information at http://blogs.msdn.com/sqlservercompact/archive/2007/01/13/windows-2000-support.aspx

    Thursday, March 22, 2007 12:59 PM

All replies

  • You are certainly not out of scope - here is the answer - some good old VB code:

    Dim pConn As ADODB.Connection

        Dim pRS As ADODB.Recordset

        Dim sStr As String

     

        Set pConn = New ADODB.Connection

        Set pRS = New ADODB.Recordset

      

       pConn.ConnectionString= "PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\\MyDB.sdf"

       

        pConn.Open

     

        sStr = "Select * from employee"

     

        ' Open the recordset

        pRS.Open sStr, pConn, adOpenForwardOnly

      

       Do Until pRS.EOF

     

         ' Employee table has an emp_fname column that represents the first name of employee

         ' List1 is a list control

         List1.AddItem pRS!emp_fname

         pRS.MoveNext

     

       Loop

     

       pRS.Close

       pConn.Close

     

     Set pRS = Nothing

     Set pConn = Nothing

    More information at http://blogs.msdn.com/sqlservercompact/archive/2007/01/13/windows-2000-support.aspx

    Thursday, March 22, 2007 12:59 PM
  • Thanks very much!  Finally!

    Thursday, March 22, 2007 1:41 PM
  • I'm having the same issue, and tried this code, buy I must be missing a DLL or something. I try to run the code  and get error message 

     

    Run-time error '3706':

    Provider can not be found. It may not be properly installed.

     

    I have current MDAC installed so I'm not sure what I'm missing here. Any ideas?

    Tuesday, March 27, 2007 11:46 PM
  • Hi Jay,

    You need to install the  Microsoft SQL Server 2005 Compact Edition Runtime (which also installs the OLEDB provider) available at http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=85E0C3CE-3FA1-453A-8CE9-AF6CA20946C3

     

    • Proposed as answer by Prshant Friday, June 26, 2009 7:09 AM
    Wednesday, March 28, 2007 7:09 AM
  • Thanks very much! I thought that got installed with managment studio.

    Wednesday, March 28, 2007 1:09 PM
  • You are welcome, Jay. It is distributed as a standalone database engine, with it's own MSI.
    Wednesday, March 28, 2007 1:20 PM
  • I am trying to use SQL Server Compact Edition 3.5 with Visual Basic 6.0. The example given by you above seems helpful but I do not know how to create a database (.sdf) file. Could anyone please help me on this? 
    Wednesday, June 24, 2009 12:06 PM
  • You can use SQL Server 2088 Management Studio Express for that.
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    • Proposed as answer by Prshant Friday, June 26, 2009 7:08 AM
    Wednesday, June 24, 2009 2:49 PM
  • No, it is just not supported. But you can use my command line utility (http://sqlcecmd.codeplex.com/) if you do not want to install SSMS 2008.
    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.
    • Proposed as answer by Prshant Friday, June 26, 2009 7:08 AM
    Thursday, June 25, 2009 6:57 AM
  • Hi Erik! Thank you very much for the answer. This is exactly what I was looking for. I must say you are doing a great job. Thanks once again genius! 
    Friday, June 26, 2009 7:07 AM