Re: ODBC Connection to SQL Server Compact
- From: "Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
- Date: Mon, 2 Feb 2009 13:51:00 -0500
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)
"Spiros" <Spiros@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:BEEB5F86-C20B-45B2-8688-D92BCB768D91@xxxxxxxxxxxxxxxx
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.
"Sylvain Lafontaine" wrote:
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)
"Spiros" <Spiros@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:524F5EE0-74E3-4AF0-BCD9-40CB0961E3B5@xxxxxxxxxxxxxxxx
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..
.
- Follow-Ups:
- Re: ODBC Connection to SQL Server Compact
- From: Spiros
- Re: ODBC Connection to SQL Server Compact
- References:
- Re: ODBC Connection to SQL Server Compact
- From: Sylvain Lafontaine
- Re: ODBC Connection to SQL Server Compact
- From: Spiros
- Re: ODBC Connection to SQL Server Compact
- Prev by Date: Re: Avoid Sumof, Avgof etc in new table
- Next by Date: Re: Send mail object stopped working-lost my active x control
- Previous by thread: Re: ODBC Connection to SQL Server Compact
- Next by thread: Re: ODBC Connection to SQL Server Compact
- Index(es):
Relevant Pages
|
Loading
