vba: xls to sdf (ms sql) via odbc RRS feed

  • Question

  • Hi all,

    My need is to export data from .xls to .sdf (ms sql server management studio 2008) via odbc.

    Here is my code:

    Sub Excel_InsertIntoSDF() 
        Dim oCn As ADODB.Connection 
        Dim oRS As ADODB.Recordset 
        Dim ConnString As String 
        Dim SQL As String 
        Dim DBpath As String 
        Dim qt As QueryTable 
        Dim tt As String 
        Dim dd As String 
        DBpath = "C:\MS_SQL\MyDatabase#1.sdf" 
        tt = "3" 
        dd = "Germany" 
        ConnString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=" & DBpath 
        Set oCn = New ADODB.Connection 
        oCn.Properties("Prompt") = 2 
        oCn.ConnectionString = ConnString 
        strSQL = "INSERT INTO [depo#country](countryid, countryname) VALUES('" & tt & "','" & dd & "')" 
        oCn.Execute strSQL 
        Set oCn = Nothing 
    End Sub

     It returns error at string oCn.Open:

    Run-time error '-2147467259 (80004005)':
    [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified

    How do I make it work? I guess I need proper connection string.

    ms sql server management studio 2008, ms office 2011, windows7

    Thursday, October 20, 2011 6:34 AM

All replies

  • Remove the line:

    oCn.Properties("Prompt") = 2

    The Prompt property is not supported by the OLE DB Provider for Sql Server Compact Edition.

    For example, if you FIRST specify the OleDb connection string, the Connection will initialize to use the OleDb provider. When you then try to set the Prompt property, you get an error because it doesn't exist:

        ConnString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=" & DBpath 
        Set oCn = New ADODB.Connection 
        'Set connection string first
        oCn.ConnectionString = ConnString 
        ' This will now throw an error:
        oCn.Properties("Prompt") = 2 

    Friday, October 21, 2011 5:25 PM
  • Here is my code:
    Sub ImportSqlCeData()
    Dim pConn As ADODB.Connection
    Dim pRS As ADODB.Recordset
    Dim rRS As ADODB.Recordset
    Set pConn = New ADODB.Connection
    Dim cmd As New ADODB.Command
    Set pRS = New ADODB.Recordset
    Set rRS = New ADODB.Recordset
    Dim rcmd As New ADODB.Command
    pConn.ConnectionString = "PROVIDER=Microsoft.SQLSERVER.CE.OLEDB.3.5;Data Source=" & SdfPath
    rcmd.ActiveConnection = pConn
    cmd.ActiveConnection = pConn
    Set pRS = cmd.Execute
    End Sub


     error message:


    Run-time error '-2147217843 (80040e4d)':

    Authentication failed.


    How do I call prompt for .sdf file password?

    • Edited by ymarkiv Monday, October 24, 2011 6:38 AM incorrect tags
    Monday, October 24, 2011 6:35 AM
  • I'm going to edit my last answer, because I realize now it was wrong...

    Specifically: the lack of support for the "Prompt" property, which maps to the native OLE DB promperty DBPROP_INIT_PROMPT, is due to the SQL Server Compact Edition OLE DB provider.

    It can be confusing figuring out which properties are supported by which provider, especially since a lot of the documentation for OLE DB providers is largely focused on C++ development. That said, a simple way to determine exactly which properties are supported, and what their ADO aliases are, is to enumerate the Properties cache of an ADODB.Connection object both before and after opening, but always after you set the Provider property.

    For example, to find out which extended properties are supported by the SSCE OLE DB provider:

    VBA Code

    Sub ListProviderProps()
        Dim oCn As ADODB.Connection, p As ADODB.Property
        Set oCn = New ADODB.Connection
        ' Directly set the provider
        oCn.Provider = "Microsoft.SQLSERVER.CE.OLEDB.3.5"
        ' Enumerate available properties before opening a connection
        For Each p In oCn.Properties
            Debug.Print p.Name; " = "; p.Value
        Set oCn = Nothing
    End Sub

    Output (Note: No Prompt value):

    Data Source = 
    Mode =  19 
    Locale Identifier =  1033 
    OLE DB Services = -6 
    Persist Security Info = 
    SSCE:Max Buffer Size =  4096 
    SSCE:Database Password = 
    SSCE:Encrypt Database = False
    SSCE:Default Lock Escalation =  100 
    SSCE:Temp File Directory = 
    SSCE:Default Lock Timeout =  5000 
    SSCE:AutoShrink Threshold =  60 
    SSCE:Flush Interval =  10 
    SSCE:Test Callback Pointer =  0 
    SSCE:Max Database Size =  256 
    SSCE:Temp File Max Size =  128 
    SSCE:Encryption Mode =  0 
    SSCE:Case Sensitive = False

    It is then easier to find and understand documentation like the official list of Ole DB Properties supported by the SSCE OLE DB provider: OLE DB Properties (SQL Server Compact). You can see that "SSCE:AutoShrink Threshold" corresponds to DBPROP_SSCE_AUTO_SHRINK_THRESHOLD (see Provider-Specific Properties (OLE DB)). And you can see that Initialization Properties (OLE DB) does NOT include DBPROP_INIT_PROMPT. 

    Monday, October 24, 2011 1:41 PM
  • How do I call prompt for .sdf file password?

    In case it wasn't clear, the answer to that question is: you can't.

    Monday, October 24, 2011 2:01 PM