ODBC Connection to SQL Server Compact

S

Spiros

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. In debugging mode when the line pConn.Open reached it produces the
following error:

(We suspect that there is a problem with the OLEDB provider...)

"Run-time error -2147467259 (800004005)

Method "Open" of object '_Connection' failed.

Any ideas of what it may be...?

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


Thank you in advance...

Spiros..
 
S

Sylvain Lafontaine

Your code looks fine, it's possible that you have a permission problem - for
example, a password is required - or that the database is already opened in
exclusive mode by another problem, for example either SSMS or VS.NET if you
are using one of these programs to create the SDF database file.

First, make sure that you have setup a reference to the Microsoft ActiveX
Data Objects 2.1 Library or later in the References dialog window of VBA.

Second, take a look at the errors collection returned by the connection
object; for example:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString = "Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5" _
& "Data Source=C:\Apog\Apog.sdf;"

On Error Resume Next

conn.Errors.Clear
conn.Open

If (conn.Errors.Count > 0) Then
Dim e As ADODB.Error

For Each e In conn.Errors
Debug.Print e.Description
Next

Exit Sub
End If

On Error Resume Next

If (conn.State = 1) Then
rs.Open "Select * from Table1", conn

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

Set rs = Nothing
Set conn = Nothing

The provider "SQLSERVER.MOBILE.OLEDB.3.0;" is for the older version of
SQL-Compact Edition that comes with SQL-Server 2005 (?) while
"Microsoft.SQLSERVER.CE.OLEDB.3.5;" is for the latest edition (SQL-Server
2008). For more information on the parameters for the connection string,
see:

http://www.connectionstrings.com/sql-server-2005-ce

Finally, you should clear up something: OLEDB providers are not ODBC
providers. OLEDB Providers are used primarily with ADO and ODBC with DAO
but it's also possible to use an ODBC providers directly with ADO if you use
the special provider MSDASQL as a midlayer.
 
S

Spiros

Dear Sylvain,
Thanks for your time to deal with my problem and your suggestions. As far as
the password is concerned I am sure that is no need for that since when I am
opening the sdf file from SQL Management Studio...no username or password is
required to connect to database. I also make sure, when I am running the
Access code, that no other programs concerning SQL files is running. On the
other hand I am not sure if any programs are running as "services" that could
create sharing violation. I would like your suggestions on that if you
please...I will get back to you also when I try the rest of your suggestions.
Thank you in advance.
Spiros Kostopoulos.
 
S

Sylvain Lafontaine

Did you try to take a look at the messages in the Errors collection returned
by the Connection object as sugested?
 
S

Spiros

Hi,
Thanks for the code again I did try it, and I found out the following...
1)By using the connection string "Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\Apog\Apog.sdf"

I got Error Message: "The database file has been created by an earlier
version of SQL Server Compact. Please upgrade using SqlCeEngine.Upgrade()
method. [,,,,,]"

2)By using the connection string
"Conn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data
Source=C:\Apog\Apog.sdf"

The conn object opened "nicely". But Rs recordset failed to recognise the
fields name (of the table) on the Apog database.
On Debugging Mode the Expression Rs![ItemCode]= LclRs("ItemCode") the field
"ItemCode" of Rs recordset was not updated and putting the cursor the msg
"Item not found in the collection..." Following that I checked the Field name
of the "StockItems" table in the Apog.sdf (using SQL Management Studio) but
I did not find any mistakes (mispellening etc..)
Any clues? (The code follows...)
Thank you in advance..
Kind Regards...
Spiros.

Sub SyncStockItems()

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim LclRs As ADODB.Recordset
Dim SqlStr, 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

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

Conn.Open

'Delete All Records from Mobile Database
RetVal = SysCmd(acSysCmdSetStatus, "Deleting Records from Mobile
Database....")
Conn.Execute "DELETE * FROM StockItems"
'Conn.
RetVal = SysCmd(acSysCmdSetStatus, "Records Deleted from Mobile Database.")
Rs.Open "StockItems", Conn, adOpenForwardOnly, adLockOptimistic

'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.")
I = 1
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
 
S

Sylvain Lafontaine

First, make sure that the RS is not empty. Second, print the name of each
field of the RS recordset (and their other properties as well, too) by using
their numerical index (Rs(0), Rs(1), ...). With that, probably that you
should be able to see what's wrong.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Spiros said:
Hi,
Thanks for the code again I did try it, and I found out the following...
1)By using the connection string "Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\Apog\Apog.sdf"

I got Error Message: "The database file has been created by an earlier
version of SQL Server Compact. Please upgrade using SqlCeEngine.Upgrade()
method. [,,,,,]"

2)By using the connection string
"Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data
Source=C:\Apog\Apog.sdf"

The conn object opened "nicely". But Rs recordset failed to recognise the
fields name (of the table) on the Apog database.
On Debugging Mode the Expression Rs![ItemCode]= LclRs("ItemCode") the
field
"ItemCode" of Rs recordset was not updated and putting the cursor the msg
"Item not found in the collection..." Following that I checked the Field
name
of the "StockItems" table in the Apog.sdf (using SQL Management Studio)
but
I did not find any mistakes (mispellening etc..)
Any clues? (The code follows...)
Thank you in advance..
Kind Regards...
Spiros.

Sub SyncStockItems()

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim LclRs As ADODB.Recordset
Dim SqlStr, 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

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

Conn.Open

'Delete All Records from Mobile Database
RetVal = SysCmd(acSysCmdSetStatus, "Deleting Records from Mobile
Database....")
Conn.Execute "DELETE * FROM StockItems"
'Conn.
RetVal = SysCmd(acSysCmdSetStatus, "Records Deleted from Mobile
Database.")
Rs.Open "StockItems", Conn, adOpenForwardOnly, adLockOptimistic

'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.")
I = 1
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


Sylvain Lafontaine said:
Did you try to take a look at the messages in the Errors collection
returned
by the Connection object as sugested?
Click to expand...
Click to expand...
 
S

Spiros

Hi Sylvain,
I have tried all the possibilities...but with no luck so far..
1)First the "conn" object opens giving no errors in the error collection..
2)Secondly the RS recordset opens nicely but when I try to access the
objects (RS(0), RS(1)...) I get an error message "Run time error '3265'. Item
not found in the collection...
Finally I thought to use the Conn.Execute "SqlStr" statement where SqlStr is
"INSERT INTO StockItems (ItemCode, OnBoxCode, FactoryCode, Description)
VALUES (1, '14158-001', '8431319393071', 'Antik Negro/Tee Marron')". I tried
as query in SQL Management Studio and it worked fine and added a new record
in the table "StockItems" of the Apog.sdf....BUT it does not work from
Access...
I think this piece of code is making fun of me...
Any ideas?
Thank you for your time again.
Kind Regards,
Spiros.


Sylvain Lafontaine said:
First, make sure that the RS is not empty. Second, print the name of each
field of the RS recordset (and their other properties as well, too) by using
their numerical index (Rs(0), Rs(1), ...). With that, probably that you
should be able to see what's wrong.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Spiros said:
Hi,
Thanks for the code again I did try it, and I found out the following...
1)By using the connection string "Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=C:\\Apog\Apog.sdf"

I got Error Message: "The database file has been created by an earlier
version of SQL Server Compact. Please upgrade using SqlCeEngine.Upgrade()
method. [,,,,,]"

2)By using the connection string
"Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data
Source=C:\Apog\Apog.sdf"

The conn object opened "nicely". But Rs recordset failed to recognise the
fields name (of the table) on the Apog database.
On Debugging Mode the Expression Rs![ItemCode]= LclRs("ItemCode") the
field
"ItemCode" of Rs recordset was not updated and putting the cursor the msg
"Item not found in the collection..." Following that I checked the Field
name
of the "StockItems" table in the Apog.sdf (using SQL Management Studio)
but
I did not find any mistakes (mispellening etc..)
Any clues? (The code follows...)
Thank you in advance..
Kind Regards...
Spiros.

Sub SyncStockItems()

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim LclRs As ADODB.Recordset
Dim SqlStr, 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

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

Conn.Open

'Delete All Records from Mobile Database
RetVal = SysCmd(acSysCmdSetStatus, "Deleting Records from Mobile
Database....")
Conn.Execute "DELETE * FROM StockItems"
'Conn.
RetVal = SysCmd(acSysCmdSetStatus, "Records Deleted from Mobile
Database.")
Rs.Open "StockItems", Conn, adOpenForwardOnly, adLockOptimistic

'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.")
I = 1
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


Sylvain Lafontaine said:
Did you try to take a look at the messages in the Errors collection
returned
by the Connection object as sugested?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Dear Sylvain,
Thanks for your time to deal with my problem and your suggestions. As
far
as
the password is concerned I am sure that is no need for that since when
I
am
opening the sdf file from SQL Management Studio...no username or
password
is
required to connect to database. I also make sure, when I am running
the
Access code, that no other programs concerning SQL files is running. On
the
other hand I am not sure if any programs are running as "services" that
could
create sharing violation. I would like your suggestions on that if you
please...I will get back to you also when I try the rest of your
suggestions.
Thank you in advance.
Spiros Kostopoulos.

:

Your code looks fine, it's possible that you have a permission
problem -
for
example, a password is required - or that the database is already
opened
in
exclusive mode by another problem, for example either SSMS or VS.NET
if
you
are using one of these programs to create the SDF database file.

First, make sure that you have setup a reference to the Microsoft
ActiveX
Data Objects 2.1 Library or later in the References dialog window of
VBA.

Second, take a look at the errors collection returned by the
connection
object; for example:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString =
"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5" _
& "Data Source=C:\Apog\Apog.sdf;"

On Error Resume Next

conn.Errors.Clear
conn.Open

If (conn.Errors.Count > 0) Then
Dim e As ADODB.Error

For Each e In conn.Errors
Debug.Print e.Description
Next

Exit Sub
End If

On Error Resume Next

If (conn.State = 1) Then
rs.Open "Select * from Table1", conn

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

Set rs = Nothing
Set conn = Nothing

The provider "SQLSERVER.MOBILE.OLEDB.3.0;" is for the older version of
SQL-Compact Edition that comes with SQL-Server 2005 (?) while
"Microsoft.SQLSERVER.CE.OLEDB.3.5;" is for the latest edition
(SQL-Server
2008). For more information on the parameters for the connection
string,
see:

http://www.connectionstrings.com/sql-server-2005-ce

Finally, you should clear up something: OLEDB providers are not ODBC
providers. OLEDB Providers are used primarily with ADO and ODBC with
DAO
but it's also possible to use an ODBC providers directly with ADO if
you
use
the special provider MSDASQL as a midlayer.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


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. In debugging mode when the line pConn.Open reached it produces
the
following error:

(We suspect that there is a problem with the OLEDB provider...)

"Run-time error -2147467259 (800004005)

Method "Open" of object '_Connection' failed.

Any ideas of what it may be...?

...(For the purpose of testing I used the following code)...
Sub SyncStockItems()
Click to expand...
Click to expand...
Click to expand...
 
S

Sylvain Lafontaine

Possibly because you are trying to use two open recordset on the same
connection object. A connection object must be used with a single recordset
at a time and any previous recordset that has used this connection must have
been closed before opening a new one.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Spiros said:
Hi Sylvain,
I have tried all the possibilities...but with no luck so far..
1)First the "conn" object opens giving no errors in the error collection..
2)Secondly the RS recordset opens nicely but when I try to access the
objects (RS(0), RS(1)...) I get an error message "Run time error '3265'.
Item
not found in the collection...
Finally I thought to use the Conn.Execute "SqlStr" statement where SqlStr
is
"INSERT INTO StockItems (ItemCode, OnBoxCode, FactoryCode, Description)
VALUES (1, '14158-001', '8431319393071', 'Antik Negro/Tee Marron')". I
tried
as query in SQL Management Studio and it worked fine and added a new
record
in the table "StockItems" of the Apog.sdf....BUT it does not work from
Access...
I think this piece of code is making fun of me...
Any ideas?
Thank you for your time again.
Kind Regards,
Spiros.


Sylvain Lafontaine said:
First, make sure that the RS is not empty. Second, print the name of
each
field of the RS recordset (and their other properties as well, too) by
using
their numerical index (Rs(0), Rs(1), ...). With that, probably that you
should be able to see what's wrong.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Spiros said:
Hi,
Thanks for the code again I did try it, and I found out the
following...
1)By using the connection string "Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data
Source=C:\\Apog\Apog.sdf"

I got Error Message: "The database file has been created by an earlier
version of SQL Server Compact. Please upgrade using
SqlCeEngine.Upgrade()
method. [,,,,,]"

2)By using the connection string
"Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data
Source=C:\Apog\Apog.sdf"

The conn object opened "nicely". But Rs recordset failed to recognise
the
fields name (of the table) on the Apog database.
On Debugging Mode the Expression Rs![ItemCode]= LclRs("ItemCode") the
field
"ItemCode" of Rs recordset was not updated and putting the cursor the
msg
"Item not found in the collection..." Following that I checked the
Field
name
of the "StockItems" table in the Apog.sdf (using SQL Management
Studio)
but
I did not find any mistakes (mispellening etc..)
Any clues? (The code follows...)
Thank you in advance..
Kind Regards...
Spiros.

Sub SyncStockItems()

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim LclRs As ADODB.Recordset
Dim SqlStr, 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

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

Conn.Open

'Delete All Records from Mobile Database
RetVal = SysCmd(acSysCmdSetStatus, "Deleting Records from Mobile
Database....")
Conn.Execute "DELETE * FROM StockItems"
'Conn.
RetVal = SysCmd(acSysCmdSetStatus, "Records Deleted from Mobile
Database.")
Rs.Open "StockItems", Conn, adOpenForwardOnly, adLockOptimistic

'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.")
I = 1
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


:

Did you try to take a look at the messages in the Errors collection
returned
by the Connection object as sugested?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Dear Sylvain,
Thanks for your time to deal with my problem and your suggestions.
As
far
as
the password is concerned I am sure that is no need for that since
when
I
am
opening the sdf file from SQL Management Studio...no username or
password
is
required to connect to database. I also make sure, when I am running
the
Access code, that no other programs concerning SQL files is running.
On
the
other hand I am not sure if any programs are running as "services"
that
could
create sharing violation. I would like your suggestions on that if
you
please...I will get back to you also when I try the rest of your
suggestions.
Thank you in advance.
Spiros Kostopoulos.

:

Your code looks fine, it's possible that you have a permission
problem -
for
example, a password is required - or that the database is already
opened
in
exclusive mode by another problem, for example either SSMS or
VS.NET
if
you
are using one of these programs to create the SDF database file.

First, make sure that you have setup a reference to the Microsoft
ActiveX
Data Objects 2.1 Library or later in the References dialog window
of
VBA.

Second, take a look at the errors collection returned by the
connection
object; for example:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString =
"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5" _
& "Data Source=C:\Apog\Apog.sdf;"

On Error Resume Next

conn.Errors.Clear
conn.Open

If (conn.Errors.Count > 0) Then
Dim e As ADODB.Error

For Each e In conn.Errors
Debug.Print e.Description
Next

Exit Sub
End If

On Error Resume Next

If (conn.State = 1) Then
rs.Open "Select * from Table1", conn

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

Set rs = Nothing
Set conn = Nothing

The provider "SQLSERVER.MOBILE.OLEDB.3.0;" is for the older version
of
SQL-Compact Edition that comes with SQL-Server 2005 (?) while
"Microsoft.SQLSERVER.CE.OLEDB.3.5;" is for the latest edition
(SQL-Server
2008). For more information on the parameters for the connection
string,
see:

http://www.connectionstrings.com/sql-server-2005-ce

Finally, you should clear up something: OLEDB providers are not
ODBC
providers. OLEDB Providers are used primarily with ADO and ODBC
with
DAO
but it's also possible to use an ODBC providers directly with ADO
if
you
use
the special provider MSDASQL as a midlayer.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


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. In debugging mode when the line pConn.Open reached it
produces
the
following error:

(We suspect that there is a problem with the OLEDB provider...)

"Run-time error -2147467259 (800004005)

Method "Open" of object '_Connection' failed.

Any ideas of what it may be...?

...(For the purpose of testing I used the following code)...
Sub SyncStockItems()
Click to expand...
Click to expand...
Click to expand...
 
S

Spiros

Thanks again...I tried both options:
1) Use only 'execute' commands from con objet with no Rs (recordset) used.
2) Use only Rs recordset without using any commands from con object...
Both of them failded to add a new record on the remote database...What is
going wrong?

Regards,
Spiros.


Sylvain Lafontaine said:
Possibly because you are trying to use two open recordset on the same
connection object. A connection object must be used with a single recordset
at a time and any previous recordset that has used this connection must have
been closed before opening a new one.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Spiros said:
Hi Sylvain,
I have tried all the possibilities...but with no luck so far..
1)First the "conn" object opens giving no errors in the error collection..
2)Secondly the RS recordset opens nicely but when I try to access the
objects (RS(0), RS(1)...) I get an error message "Run time error '3265'.
Item
not found in the collection...
Finally I thought to use the Conn.Execute "SqlStr" statement where SqlStr
is
"INSERT INTO StockItems (ItemCode, OnBoxCode, FactoryCode, Description)
VALUES (1, '14158-001', '8431319393071', 'Antik Negro/Tee Marron')". I
tried
as query in SQL Management Studio and it worked fine and added a new
record
in the table "StockItems" of the Apog.sdf....BUT it does not work from
Access...
I think this piece of code is making fun of me...
Any ideas?
Thank you for your time again.
Kind Regards,
Spiros.


Sylvain Lafontaine said:
First, make sure that the RS is not empty. Second, print the name of
each
field of the RS recordset (and their other properties as well, too) by
using
their numerical index (Rs(0), Rs(1), ...). With that, probably that you
should be able to see what's wrong.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Hi,
Thanks for the code again I did try it, and I found out the
following...
1)By using the connection string "Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data
Source=C:\\Apog\Apog.sdf"

I got Error Message: "The database file has been created by an earlier
version of SQL Server Compact. Please upgrade using
SqlCeEngine.Upgrade()
method. [,,,,,]"

2)By using the connection string
"Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data
Source=C:\Apog\Apog.sdf"

The conn object opened "nicely". But Rs recordset failed to recognise
the
fields name (of the table) on the Apog database.
On Debugging Mode the Expression Rs![ItemCode]= LclRs("ItemCode") the
field
"ItemCode" of Rs recordset was not updated and putting the cursor the
msg
"Item not found in the collection..." Following that I checked the
Field
name
of the "StockItems" table in the Apog.sdf (using SQL Management
Studio)
but
I did not find any mistakes (mispellening etc..)
Any clues? (The code follows...)
Thank you in advance..
Kind Regards...
Spiros.

Sub SyncStockItems()

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim LclRs As ADODB.Recordset
Dim SqlStr, 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

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

Conn.Open

'Delete All Records from Mobile Database
RetVal = SysCmd(acSysCmdSetStatus, "Deleting Records from Mobile
Database....")
Conn.Execute "DELETE * FROM StockItems"
'Conn.
RetVal = SysCmd(acSysCmdSetStatus, "Records Deleted from Mobile
Database.")
Rs.Open "StockItems", Conn, adOpenForwardOnly, adLockOptimistic

'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.")
I = 1
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


:

Did you try to take a look at the messages in the Errors collection
returned
by the Connection object as sugested?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Dear Sylvain,
Thanks for your time to deal with my problem and your suggestions.
As
far
as
the password is concerned I am sure that is no need for that since
when
I
am
opening the sdf file from SQL Management Studio...no username or
password
is
required to connect to database. I also make sure, when I am running
the
Access code, that no other programs concerning SQL files is running.
On
the
other hand I am not sure if any programs are running as "services"
that
could
create sharing violation. I would like your suggestions on that if
you
please...I will get back to you also when I try the rest of your
suggestions.
Thank you in advance.
Spiros Kostopoulos.

:

Your code looks fine, it's possible that you have a permission
problem -
for
example, a password is required - or that the database is already
opened
in
exclusive mode by another problem, for example either SSMS or
VS.NET
if
you
are using one of these programs to create the SDF database file.

First, make sure that you have setup a reference to the Microsoft
ActiveX
Data Objects 2.1 Library or later in the References dialog window
of
VBA.

Second, take a look at the errors collection returned by the
connection
object; for example:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString =
"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5" _
& "Data Source=C:\Apog\Apog.sdf;"

On Error Resume Next

conn.Errors.Clear
conn.Open

If (conn.Errors.Count > 0) Then
Dim e As ADODB.Error

For Each e In conn.Errors
Debug.Print e.Description
Next

Exit Sub
End If

On Error Resume Next

If (conn.State = 1) Then
rs.Open "Select * from Table1", conn

While Not rs.EOF
Debug.Print rs(0) & ": " & rs(1)
rs.MoveNext
Wend

End If
Click to expand...
Click to expand...
Click to expand...
 
S

Spiros

Sylvain Lafontaine said:
Possibly because you are trying to use two open recordset on the same
connection object. A connection object must be used with a single recordset
at a time and any previous recordset that has used this connection must have
been closed before opening a new one.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Spiros said:
Hi Sylvain,
I have tried all the possibilities...but with no luck so far..
1)First the "conn" object opens giving no errors in the error collection..
2)Secondly the RS recordset opens nicely but when I try to access the
objects (RS(0), RS(1)...) I get an error message "Run time error '3265'.
Item
not found in the collection...
Finally I thought to use the Conn.Execute "SqlStr" statement where SqlStr
is
"INSERT INTO StockItems (ItemCode, OnBoxCode, FactoryCode, Description)
VALUES (1, '14158-001', '8431319393071', 'Antik Negro/Tee Marron')". I
tried
as query in SQL Management Studio and it worked fine and added a new
record
in the table "StockItems" of the Apog.sdf....BUT it does not work from
Access...
I think this piece of code is making fun of me...
Any ideas?
Thank you for your time again.
Kind Regards,
Spiros.


Sylvain Lafontaine said:
First, make sure that the RS is not empty. Second, print the name of
each
field of the RS recordset (and their other properties as well, too) by
using
their numerical index (Rs(0), Rs(1), ...). With that, probably that you
should be able to see what's wrong.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Hi,
Thanks for the code again I did try it, and I found out the
following...
1)By using the connection string "Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data
Source=C:\\Apog\Apog.sdf"

I got Error Message: "The database file has been created by an earlier
version of SQL Server Compact. Please upgrade using
SqlCeEngine.Upgrade()
method. [,,,,,]"

2)By using the connection string
"Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data
Source=C:\Apog\Apog.sdf"

The conn object opened "nicely". But Rs recordset failed to recognise
the
fields name (of the table) on the Apog database.
On Debugging Mode the Expression Rs![ItemCode]= LclRs("ItemCode") the
field
"ItemCode" of Rs recordset was not updated and putting the cursor the
msg
"Item not found in the collection..." Following that I checked the
Field
name
of the "StockItems" table in the Apog.sdf (using SQL Management
Studio)
but
I did not find any mistakes (mispellening etc..)
Any clues? (The code follows...)
Thank you in advance..
Kind Regards...
Spiros.

Sub SyncStockItems()

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim LclRs As ADODB.Recordset
Dim SqlStr, 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

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

Conn.Open

'Delete All Records from Mobile Database
RetVal = SysCmd(acSysCmdSetStatus, "Deleting Records from Mobile
Database....")
Conn.Execute "DELETE * FROM StockItems"
'Conn.
RetVal = SysCmd(acSysCmdSetStatus, "Records Deleted from Mobile
Database.")
Rs.Open "StockItems", Conn, adOpenForwardOnly, adLockOptimistic

'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.")
I = 1
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


:

Did you try to take a look at the messages in the Errors collection
returned
by the Connection object as sugested?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Dear Sylvain,
Thanks for your time to deal with my problem and your suggestions.
As
far
as
the password is concerned I am sure that is no need for that since
when
I
am
opening the sdf file from SQL Management Studio...no username or
password
is
required to connect to database. I also make sure, when I am running
the
Access code, that no other programs concerning SQL files is running.
On
the
other hand I am not sure if any programs are running as "services"
that
could
create sharing violation. I would like your suggestions on that if
you
please...I will get back to you also when I try the rest of your
suggestions.
Thank you in advance.
Spiros Kostopoulos.

:

Your code looks fine, it's possible that you have a permission
problem -
for
example, a password is required - or that the database is already
opened
in
exclusive mode by another problem, for example either SSMS or
VS.NET
if
you
are using one of these programs to create the SDF database file.

First, make sure that you have setup a reference to the Microsoft
ActiveX
Data Objects 2.1 Library or later in the References dialog window
of
VBA.

Second, take a look at the errors collection returned by the
connection
object; for example:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString =
"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5" _
& "Data Source=C:\Apog\Apog.sdf;"

On Error Resume Next

conn.Errors.Clear
conn.Open

If (conn.Errors.Count > 0) Then
Dim e As ADODB.Error

For Each e In conn.Errors
Debug.Print e.Description
Next

Exit Sub
End If

On Error Resume Next

If (conn.State = 1) Then
rs.Open "Select * from Table1", conn

While Not rs.EOF
Debug.Print rs(0) & ": " & rs(1)
rs.MoveNext
Wend

End If
Click to expand...
Click to expand...
Click to expand...
 
S

Sylvain Lafontaine

Sorry for the delay but I have to reinstall my machine these last days.

I have no idea what's going wrong. It's your problem only with inserting
new records from ADO or if you can't read any record from the database as
well?

I will try some test this weekend but as I don't have the save version as
you (I have the 3.5), I'm not sure if I will be able to reproduce your
problem. Last time I checked, I don't remember having any problem using
this OLEDB driver from VBA.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Spiros said:
Thanks again...I tried both options:
1) Use only 'execute' commands from con objet with no Rs (recordset) used.
2) Use only Rs recordset without using any commands from con object...
Both of them failded to add a new record on the remote database...What is
going wrong?

Regards,
Spiros.


Sylvain Lafontaine said:
Possibly because you are trying to use two open recordset on the same
connection object. A connection object must be used with a single
recordset
at a time and any previous recordset that has used this connection must
have
been closed before opening a new one.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Spiros said:
Hi Sylvain,
I have tried all the possibilities...but with no luck so far..
1)First the "conn" object opens giving no errors in the error
collection..
2)Secondly the RS recordset opens nicely but when I try to access the
objects (RS(0), RS(1)...) I get an error message "Run time error
'3265'.
Item
not found in the collection...
Finally I thought to use the Conn.Execute "SqlStr" statement where
SqlStr
is
"INSERT INTO StockItems (ItemCode, OnBoxCode, FactoryCode, Description)
VALUES (1, '14158-001', '8431319393071', 'Antik Negro/Tee Marron')". I
tried
as query in SQL Management Studio and it worked fine and added a new
record
in the table "StockItems" of the Apog.sdf....BUT it does not work from
Access...
I think this piece of code is making fun of me...
Any ideas?
Thank you for your time again.
Kind Regards,
Spiros.


:

First, make sure that the RS is not empty. Second, print the name of
each
field of the RS recordset (and their other properties as well, too) by
using
their numerical index (Rs(0), Rs(1), ...). With that, probably that
you
should be able to see what's wrong.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and
SQL-Server
(French)


Hi,
Thanks for the code again I did try it, and I found out the
following...
1)By using the connection string "Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data
Source=C:\\Apog\Apog.sdf"

I got Error Message: "The database file has been created by an
earlier
version of SQL Server Compact. Please upgrade using
SqlCeEngine.Upgrade()
method. [,,,,,]"

2)By using the connection string
"Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data
Source=C:\Apog\Apog.sdf"

The conn object opened "nicely". But Rs recordset failed to
recognise
the
fields name (of the table) on the Apog database.
On Debugging Mode the Expression Rs![ItemCode]= LclRs("ItemCode")
the
field
"ItemCode" of Rs recordset was not updated and putting the cursor
the
msg
"Item not found in the collection..." Following that I checked the
Field
name
of the "StockItems" table in the Apog.sdf (using SQL Management
Studio)
but
I did not find any mistakes (mispellening etc..)
Any clues? (The code follows...)
Thank you in advance..
Kind Regards...
Spiros.

Sub SyncStockItems()

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim LclRs As ADODB.Recordset
Dim SqlStr, 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

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

Conn.Open

'Delete All Records from Mobile Database
RetVal = SysCmd(acSysCmdSetStatus, "Deleting Records from Mobile
Database....")
Conn.Execute "DELETE * FROM StockItems"
'Conn.
RetVal = SysCmd(acSysCmdSetStatus, "Records Deleted from Mobile
Database.")
Rs.Open "StockItems", Conn, adOpenForwardOnly, adLockOptimistic

'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.")
I = 1
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


:

Did you try to take a look at the messages in the Errors collection
returned
by the Connection object as sugested?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Dear Sylvain,
Thanks for your time to deal with my problem and your
suggestions.
As
far
as
the password is concerned I am sure that is no need for that
since
when
I
am
opening the sdf file from SQL Management Studio...no username or
password
is
required to connect to database. I also make sure, when I am
running
the
Access code, that no other programs concerning SQL files is
running.
On
the
other hand I am not sure if any programs are running as
"services"
that
could
create sharing violation. I would like your suggestions on that
if
you
please...I will get back to you also when I try the rest of your
suggestions.
Thank you in advance.
Spiros Kostopoulos.

:

Your code looks fine, it's possible that you have a permission
problem -
for
example, a password is required - or that the database is
already
opened
in
exclusive mode by another problem, for example either SSMS or
VS.NET
if
you
are using one of these programs to create the SDF database file.

First, make sure that you have setup a reference to the
Microsoft
ActiveX
Data Objects 2.1 Library or later in the References dialog
window
of
VBA.

Second, take a look at the errors collection returned by the
connection
object; for example:

Dim conn As ADODB.Connection
Dim rs As ADODB.Recordset

Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset

conn.ConnectionString =
"Provider=Microsoft.SQLSERVER.CE.OLEDB.3.5" _
& "Data Source=C:\Apog\Apog.sdf;"

On Error Resume Next

conn.Errors.Clear
conn.Open

If (conn.Errors.Count > 0) Then
Dim e As ADODB.Error

For Each e In conn.Errors
Debug.Print e.Description
Next

Exit Sub
End If

On Error Resume Next

If (conn.State = 1) Then
rs.Open "Select * from Table1", conn

While Not rs.EOF
Debug.Print rs(0) & ": " & rs(1)
rs.MoveNext
Wend

End If
Click to expand...
Click to expand...
Click to expand...
 
S

Spiros

Both of them (i.e. no adding records and no viewing the records neither)...
Thanks again.
Spiros.

Sylvain Lafontaine said:
Sorry for the delay but I have to reinstall my machine these last days.

I have no idea what's going wrong. It's your problem only with inserting
new records from ADO or if you can't read any record from the database as
well?

I will try some test this weekend but as I don't have the save version as
you (I have the 3.5), I'm not sure if I will be able to reproduce your
problem. Last time I checked, I don't remember having any problem using
this OLEDB driver from VBA.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)


Spiros said:
Thanks again...I tried both options:
1) Use only 'execute' commands from con objet with no Rs (recordset) used.
2) Use only Rs recordset without using any commands from con object...
Both of them failded to add a new record on the remote database...What is
going wrong?

Regards,
Spiros.


Sylvain Lafontaine said:
Possibly because you are trying to use two open recordset on the same
connection object. A connection object must be used with a single
recordset
at a time and any previous recordset that has used this connection must
have
been closed before opening a new one.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)



Hi Sylvain,
I have tried all the possibilities...but with no luck so far..
1)First the "conn" object opens giving no errors in the error
collection..
2)Secondly the RS recordset opens nicely but when I try to access the
objects (RS(0), RS(1)...) I get an error message "Run time error
'3265'.
Item
not found in the collection...
Finally I thought to use the Conn.Execute "SqlStr" statement where
SqlStr
is
"INSERT INTO StockItems (ItemCode, OnBoxCode, FactoryCode, Description)
VALUES (1, '14158-001', '8431319393071', 'Antik Negro/Tee Marron')". I
tried
as query in SQL Management Studio and it worked fine and added a new
record
in the table "StockItems" of the Apog.sdf....BUT it does not work from
Access...
I think this piece of code is making fun of me...
Any ideas?
Thank you for your time again.
Kind Regards,
Spiros.


:

First, make sure that the RS is not empty. Second, print the name of
each
field of the RS recordset (and their other properties as well, too) by
using
their numerical index (Rs(0), Rs(1), ...). With that, probably that
you
should be able to see what's wrong.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain aei ca (fill the blanks, no spam please)
Independent consultant and remote programming for Access and
SQL-Server
(French)


Hi,
Thanks for the code again I did try it, and I found out the
following...
1)By using the connection string "Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data
Source=C:\\Apog\Apog.sdf"

I got Error Message: "The database file has been created by an
earlier
version of SQL Server Compact. Please upgrade using
SqlCeEngine.Upgrade()
method. [,,,,,]"

2)By using the connection string
"Conn.ConnectionString =
"PROVIDER=Microsoft.SQLSERVER.MOBILE.OLEDB.3.0;Data
Source=C:\Apog\Apog.sdf"

The conn object opened "nicely". But Rs recordset failed to
recognise
the
fields name (of the table) on the Apog database.
On Debugging Mode the Expression Rs![ItemCode]= LclRs("ItemCode")
the
field
"ItemCode" of Rs recordset was not updated and putting the cursor
the
msg
"Item not found in the collection..." Following that I checked the
Field
name
of the "StockItems" table in the Apog.sdf (using SQL Management
Studio)
but
I did not find any mistakes (mispellening etc..)
Any clues? (The code follows...)
Thank you in advance..
Kind Regards...
Spiros.

Sub SyncStockItems()

Dim Conn As ADODB.Connection
Dim Rs As ADODB.Recordset
Dim LclRs As ADODB.Recordset
Dim SqlStr, 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

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

Conn.Open

'Delete All Records from Mobile Database
RetVal = SysCmd(acSysCmdSetStatus, "Deleting Records from Mobile
Database....")
Conn.Execute "DELETE * FROM StockItems"
'Conn.
RetVal = SysCmd(acSysCmdSetStatus, "Records Deleted from Mobile
Database.")
Rs.Open "StockItems", Conn, adOpenForwardOnly, adLockOptimistic

'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.")
I = 1
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


:

Did you try to take a look at the messages in the Errors collection
returned
by the Connection object as sugested?

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


Dear Sylvain,
Thanks for your time to deal with my problem and your
suggestions.
As
far
as
the password is concerned I am sure that is no need for that
since
when
I
am
opening the sdf file from SQL Management Studio...no username or
password
is
required to connect to database. I also make sure, when I am
running
the
Access code, that no other programs concerning SQL files is
running.
On
the
other hand I am not sure if any programs are running as
"services"
that
could
create sharing violation. I would like your suggestions on that
if
you
please...I will get back to you also when I try the rest of your
suggestions.
Thank you in advance.
Spiros Kostopoulos.

:

Your code looks fine, it's possible that you have a permission
problem -
for
example, a password is required - or that the database is
already
opened
in
exclusive mode by another problem, for example either SSMS or
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

VBA Excel - Select a value from one cell in Excel to update a SQL table 1
Problem inserting time values from Access into SQL Server 5
Connection String - SQL Server Express 5
SQL Syntax error 7
Update/Refresh of linked SQL ODBC .Connect string bloats Database 1
Database Connection to SQL from Access 2003 5
Test Connection to SQL Server 7
Connecting to SQL Server via ADO 7

Top