none
VBA to access SQL Server Compact

    Question

  •  

    Dear All,

    I wrote VBA code in Access 2003 for a project...I have for a PDA...and therefore it is required to access SQL Server Compact 3.5

    Following that I came up with a problem though which I cannot find an aswer...(For the purpose of testing I used the following code)...

    Sub SyncStockItems()

    Dim pConn As ADODB.Connection
    Dim pRs As ADODB.Recordset

    Set pConn = New ADODB.Connection
    Set pRs = New ADODB.Recordset

    pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\Apog\Apog.sdf"

    -----'pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\Apog\Apog.sdf"
     If pConn.State = 1 Then
           pConn.Close
    End If
    pConn.Open

    'Delete All Records from Mobile Database
    pConn.Execute "DELETE * FROM StockItems"
    'pRs.Open

    pRs.Close
    pConn.Close

    Set pRs = Nothing
    Set pConn = Nothing

    In debugging mode when the line pConn.Open reached it produces the following error:

    "Run-time error -2147467259 (800004005)

    Method "Open" of object '_Connection' failed.

    Any ideas of what it may be...?

    Thank you in advance...

    Spiros..

    Friday, January 30, 2009 1:08 PM

All replies

  • Does the database have a password? Do you have write access to the c:\apog folder? Is SQL Compact 3.5 properly installed?


    Erik Ejlskov Jensen, MCTS: WM App, MCITP: SQL 2008 Dev - http://erikej.blogspot.com Please mark as answer, if this was it.

    Friday, January 30, 2009 2:54 PM
    Moderator
  • Dear Erik,

    Thanks for your answer...In order of query

    1) Using the SQL Management Studio to connect to the C:\Apog\Apog.sdf the boxes are as follows:

    Server Type: SQL Server Compact Edition

    Database File: C:\Apog\Apog.sdf

    Authentication: SQL Server Compact Edition Authentication

    Login: <empty>

    Password: <empty>

    by pressing "Connect" Object Explorer of the Database opens...(tables etc.)

    So I am guessing no user name and password is required.

    2) Using the Windows Explorer I checked the C:\Apog folder which shows to be O.K. in terms of attributes..()

    3) Finally I have no idea how to check the installation of SQL Server Compact. (can you suggest anything?)

    If you have any suggestions I am happy to consider them...

    Thanks for your time,

    Spiros. 

    Friday, January 30, 2009 5:14 PM
  • Hello Spiros,

    Since you are able to access your database from SSMS, I think SQL Server Compact 3.5 is properly installed. Can you please clarify where are you debugging the code? After running it in the PDA or from Project Development System itself. The Open method for SqlCeConnection fails when the data source specified does not exist or is corrupt. [http://msdn.microsoft.com/en-us/library/system.data.sqlserverce.sqlceconnection.open(VS.85).aspx] Since your database file exists and is not corrupt and again you are providing proper connection string, I think you need to clarify a bit where are you running the application.

    Please reply so that we can find out the root cause behind this problem and solve it.

    Thanks,
    Imran Siddique.
    “This posting is provided "AS IS" with no warranties, and confers no rights”.
    Saturday, January 31, 2009 5:31 AM
  • Dear Imran,

    Thanks for the time taken to go through my problem. I terms of the project I will first explain that I started by trying to "connect" the Access database with the SQL Server Compact Edition since it is a task that is crucial for the users of the project. So I wrote the code supplied in Access 2003 (VBA editor) and from there I got the error message in question. The next stage would be to write Visual Basic code in Visual Studio 2005 that it would run on the PDA (using the data copied from Access on the SQL Server Compact database) for collecting data and then transfer the data to Microsoft Access for further manipulation...
    I hope I explained the project so far...Please free to contact me if you have any more questions. Thank you in advance.
    Best Regards,
    Spiros.

    Saturday, January 31, 2009 9:08 AM
  • Hello Spiros,

    Ok. Very well explained about the project. It seems the problem might be with the OLEDB provider since you mentioned that the file exisits at the proper place and again you are able to open it with SSMS. Again, just to confirm, you are using SQL Compact 3.5 database file right? Then the connection string seems proper. I think you need to verify once more OLEDB Provider for SQL Compact 3.5 is properly installed.

    Thanks,
    Imran Siddique.
    “This posting is provided "AS IS" with no warranties, and confers no rights”.
    Saturday, January 31, 2009 11:07 AM
  •  Dear Imran,
    Thanks. I suspected that could be a problem. Any Idea where to look for OLEDB provider? a dll file that needs to be replaced? Or do you think I should post this to Access forum?
    Spiros.
    Saturday, January 31, 2009 1:10 PM
  • Hello Spiros,

    For SQL Compact 3.5, you will find the OLEDB provider DLL [sqlceoledb35.dll] under "{$0}\Program Files\Microsoft SQL Server Compact Edition\v3.5". Try repairing SQL Compact 3.5 SP1 install from here http://www.microsoft.com/Downloads/details.aspx?FamilyID=dc614aee-7e1c-4881-9c32-3a6ce53384d9&displaylang=en. And see if the problem gets solved.

    Thanks,
    Imran Siddique.
    “This posting is provided "AS IS" with no warranties, and confers no rights”.
    Saturday, January 31, 2009 2:12 PM
  • I  have upgraded the SQL Compact 3.5 SP1 from Microsoft and tried the code...the thing is I cant see the fields of neither of Rs recordset or pConn object.
    Any ideas?
    Sunday, March 01, 2009 7:14 PM
  • Thanks Spiros for trying out the steps. Can you tell me more about your findings. I am little fuzzy here. Can you please reply to my following asks:
    1. Were you able to run your app without error?
    2. If yes, then what were you looking for [Fields of objects -- Do you mean while debugging you were not able to see the object's properties]
    3. If no, then are you getting the same error?

    Thanks,
    Imran Siddique.
    “This posting is provided "AS IS" with no warranties, and confers no rights”.
    Monday, March 02, 2009 4:28 AM
  • Hi,

    What I am noticing are as follows:
    1) No record(s) are added or deleted
    2) In Debugging mode (immediate window) I request ?Rs.RecordCount and I get Error Message: "Run-time error '3704' Operation is not permited when the object is closed"
    3) Again in immediate window ?Rs.fields(0) I get error message: "Run-time error '3265' Item not found in the collection..."

    What seems not logical that since I get  no error message in neither opening conn object nor rs recordset why I have a problem connect to database...(notice that I added a record in the database by executing an INSERT query in SQL Management Studio...so that StockItems table is not empty)

    Any Ideas? Thanks in advance.
    Kind Regards,
    Spiros.

    My code is as shown below:
    Sub SyncStockItems()  
     
    Dim Conn As ADODB.Connection  
    Dim Rs As ADODB.Recordset  
    Dim LclRs As ADODB.Recordset  
    Dim SqlStr, SqlStrVal, MsgStr As String 
    Dim RecsNo As Integer, LclRecsNo As Integer 
     
     
    On Error Resume Next 
     
    Set Conn = New ADODB.Connection  
    Set Rs = New ADODB.Recordset  
    Set LclRs = New ADODB.Recordset  
     
    'LclRs.Open "StockItems", CurrentProject.Connection, adOpenKeyset, adLockOptimistic  
    'LclRecsNo = LclRs.RecordCount  
     
    'Microsoft.SQLSERVER.CE.OLEDB.3.5  
    'Conn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\Apog\Apog.sdf"  
    Conn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data Source=C:\Apog\Apog.sdf" 
     
     
     
    Conn.Errors.Clear  
    Conn.Open  
    'SqlStr = "SELECT StockItems.* FROM StockItems;"  
    'LclRs.Open SqlStr, CurrentProject.Connection, adOpenKeyset, adLockOptimistic  
     
    If (Conn.Errors.Count > 0) Then 
        Dim e As ADODB.Error 
        For Each e In Conn.Errors  
           Debug.Print e.Description  
        Next 
    End If 
     
    On Error Resume Next 
     
    Rs.Open "StockItems", Conn, adOpenForwardOnly, adLockOptimistic  
     
    If (Conn.State = 1) Then 
        While Not Rs.EOF  
            Debug.Print Rs(0) & ": " & Rs(1)  
            Rs.MoveNext  
        Wend  
    End If 
     
     
    If (Conn.State = 1) Then 
        MsgBox "On ferme...." 
        Conn.Close  
    End If 
     
     
    'Delete All Records from Mobile Database  
    RetVal = SysCmd(acSysCmdSetStatus, "Deleting Records from Mobile Database....")  
    'Conn.Execute "DELETE StockItems"  
     
    If (Conn.Errors.Count > 0) Then 
        Dim e1 As ADODB.Error 
        For Each e1 In Conn.Errors  
            Debug.Print e1.Description  
        Next 
    Else 
        RetVal = SysCmd(acSysCmdSetStatus, "Records Deleted from Mobile Database.")  
    End If 
     
    'Delete All Records from Mobile Database  
    'Rs.Open "Delete * from StockItems", Conn  
     
    'Start Copying from Local Database to Mobile Database  
    LclRs.MoveFirst  
    'RetVal = SysCmd(acSysCmdSetStatus, "Commencing Copy of Stock Items Records to Mobile Database.")  
    SqlStr = "" 
    SqlStr = "INSERT INTO StockItems (ItemCode, OnBoxCode, FactoryCode, Description) VALUES " 
    SqlStrVal = "" 
    SqlStrVal = "(" & LclRs("ItemCode") & ", " & "'" & LclRs("OnBoxCode") & "'" & ", " & "'" & LclRs("FactoryCode") & "'" & ", " & "'" & LclRs("Description") & "'" & ")" 
    SqlStr = SqlStr & SqlStrVal  
    'Conn.Execute SqlStr  
    I = 2  
    While I <= LclRecsNo  
        Rs.AddNew  
        Rs![ItemCode] = LclRs("ItemCode")  
        Rs![OnBoxCode] = LclRs("OnBoxCode")  
        Rs![FactoryCode] = LclRs("FactoryCode")  
        Rs![Description] = LclRs("Description")  
        Rs.Update  
        RecsNo = Rs.RecordCount  
        Debug.Print Rs(0) & ": " & LclRs(0)  
        Debug.Print Rs(1)  
        MsgStr = "Record " & I & " of " & LclRecsNo & "." 
        RetVal = SysCmd(acSysCmdSetStatus, MsgStr)  
        I = I + 1  
        LclRs.MoveNext  
    Wend  
     
    LclRs.Close  
    Rs.Close  
    Conn.Close  
     
    Set Rs = Nothing 
    Set Conn = Nothing 
    Set LclRs = Nothing 
     
    End Sub 
     
    Monday, March 02, 2009 8:39 PM
  • Hi,

        May be ADS (Access data synchronizer) suits your requirements. You can find information about ADS at http://social.msdn.microsoft.com/Forums/en-US/sqlce/thread/74e61cdf-4342-48b0-b413-c5f423095859/. Note: ADS is not much supported for the recent version of Compact. So, if this is only for one time use, it is fine.

    Thanks
    Udaya
    Monday, April 06, 2009 7:20 AM
  • did you get this figured out spiro? I have the same problem.
    Tuesday, May 19, 2009 7:55 PM
  • Dear Michael,
    I am sorry to say that I did not. What I am trying now is to write some code in Visual Studio with VB in order to do the trick. But since I am inexperienced with the recent versions of VB extensive reading is going on....I wonder if you have any ideas to exchange...
    Thanks,
    Spiros.
     
    Wednesday, May 20, 2009 6:02 AM
  • Dear Imran,

    I am sorry but the ADS has a lot of problems, which I can deal with...The major thing is that it does not work!

    Thanks for the suggestion though...

    Spiros

    Wednesday, May 20, 2009 6:05 AM