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..

Answer 1

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.

Answer 2

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. 

Answer 3

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”.

Answer 4

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.

Answer 5

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”.

Answer 6

 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.

Answer 7

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”.

Answer 8

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?

Answer 9

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”.

Answer 10

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

Answer 11

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

Answer 12

did you get this figured out spiro? I have the same problem.

Answer 13

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.
 

Answer 14

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