Re: 2147467259 Unexpected Error from external database driver [Non admin user]

Discussion in 'Microsoft Excel Misc' started by GS, May 10, 2012.

  1. GS

    GS Guest

    An access violation has more to do with user permissions rather than a
    problem with the provider, but in this case it may be due to the way
    you construct your connection string and/or initiate connection to the
    db.

    I use a version-aware function to set up my connection string according
    to which provider is appropriate for the running instance of Excel. My
    connection string and connection constructs are slightly different than
    yours, though!

    Example:
    Construct a connection string something like this:
    If appXL.Version => 12 Then
    'use ACE provider connection string
    Else
    'use JET provider connection string
    End If

    This precludes that I have to construct 2 separate connection strings.
    I use constants for this:

    Const sProvider As String = "Microsoft.Jet.OLEDB.4.0;"
    Const sExtProps As String = "Excel 8.0;"

    Const sProvider12 As String = "Microsoft.ACE.OLEDB.12.0;"
    Const sExtProps12 As String = "Excel 12.0 Xml;"


    You could configure your code something like this:

    <aircode>
    'Use a var to hold data source
    sDataSource = "<FullPathAndFilename>" '//edit to suit
    If appXL.Version => 12 Then
    'use ACE provider connection string
    sConnect = "Provider=" & sProvider12 & _
    "Data Source=" & sDataSource & _
    "Extended Properties=" & sExtProps12
    Else
    'use JET provider connection string
    sConnect = "Provider=" & sProvider & _
    "Data Source=" & sDataSource & _
    "Extended Properties=" & sExtProps
    End If
    </aircode>

    'Construct your SQL statement
    sSQL = "SELECT * FROM..."

    'Grab the data into a recordset
    Set rsData = New ADODB.Recordset
    rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly,
    adCmdText

    I can't speak to XL2010 because I don't have it installed yet. Apps
    using this are run on client machines and nobody's reported a problem
    with it not working in XL2010.

    <FWIW>
    I have apps that were built before XL2007 that still work in v12> using
    the JET provider because (apparently) support for that is included with
    ACE (or so I've been told)!

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 10, 2012
    #1
    1. Advertisements

  2. GS

    GS Guest

    It should be noted that the code sample in my previous reply is from a
    VB6 project that automates Excel. To use the code in Excel VBA
    projects, replace *appXL* with *Application*, OR use the *appXL*
    variable as a fully qualified reference to the Excel application
    running the code.

    --
    Garry

    Free usenet access at http://www.eternal-september.org
    ClassicVB Users Regroup!
    comp.lang.basic.visual.misc
    microsoft.public.vb.general.discussion
     
    GS, May 11, 2012
    #2
    1. Advertisements

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

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. DMc2005

    Run time error '-2147467259 (80004005) adding icon

    DMc2005, Oct 2, 2005, in forum: Microsoft Excel Misc
    Replies:
    3
    Views:
    271
    Tom Ogilvy
    Oct 2, 2005
  2. Guest
    Replies:
    1
    Views:
    2,412
    Guest
    Oct 11, 2007
  3. Guest

    System Error &H80004005 (-2147467259)

    Guest, Oct 12, 2007, in forum: Microsoft Excel Misc
    Replies:
    7
    Views:
    11,814
    Ian Digby
    Mar 31, 2011
  4. PK
    Replies:
    0
    Views:
    474
  5. PK
    Replies:
    2
    Views:
    1,039
    David Biddulph
    Dec 24, 2007
Loading...

Share This Page