Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Automation problem(Excel/Access) (2k)

    I've just started playing with automation and I've borrowed the example from SAMPLES.xls

    I've tried changing the details that I think affect me and it won't work.
    (I've tried running the example before changing it, and it doesn't work either
    but probably for different reasons.)

    With the code below;

    Sub RetrieveMydata()
    ''' NOTE: This subroutine requires that you reference the
    ''' latest version of the following library:
    '''
    ''' Microsoft ActiveX Data Objects Library

    Dim conn As ADODB.Connection
    Dim rst As ADODB.Recordset
    Dim NewBook As Workbook
    Dim PathToDatabase As String
    Dim i As Integer

    ' Create the Connection object.
    Set conn = New ADODB.Connection

    ' Set Database path string
    PathToDatabase = "C:aaaQ2workmaindatamaindataQ2_2ka.mdb"

    With conn
    'Assign the connection string to the connection object.

    .ConnectionString = "DRIVER={Microsoft dBase Driver (*.dbf)};" & _
    "DBQ=" & PathToDatabase & ";" & _
    "DefaultDir=" & PathToDatabase & ""

    ' Open the connection.
    .Open strconn
    End With

    I get an invalid path at the .ConnectionString line. I've tried looking at help but there seems to be no real help available telling me how to build it. (On this note, I can see an entry for the item "connections", "connection property", etc but clicking on them does nothing. Is there a seperate help file that might have been missed during install?)

    Also, just a possibility, how do I tell if I have the dBase driver installed?

    TIA

    Brooke

  2. #2
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation problem(Excel/Access) (2k)

    Brooke,

    Did you reference the latest version of Microsoft ActiveX Data Objects Library 2.1? I tried your code, changing the path to the database, and it did not give errors.

  3. #3
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Automation problem(Excel/Access) (2k)

    Hi Brooke,
    It may be your DefaultDir that's causing the error - it evaluates to:
    C:aaaQ2workmaindatamaindataQ2_2ka.mdb
    You could use something like:
    Sub TestADO()
    Dim conn As New ADODB.Connection
    Dim rst As New ADODB.Recordset

    'Set the provider name
    conn.Provider = "Microsoft.Jet.OLEDB.4.0"

    'Open a connection to the data
    conn.Open "G:TestsLoungetest.mdb"

    'Open a recordset with a keyset cursor
    rst.Open "tblTest", conn, adOpenKeyset
    'Copythe recordset
    Range("A1").CopyFromRecordset rst

    'Clean up
    rst.Close
    conn.Close

    End Sub
    Hope that helps.
    PS As regards the help files, are you on Win2k?
    Regards,
    Rory

    Microsoft MVP - Excel

  4. #4
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation problem(Excel/Access) (2k)

    I was actually referencing 2.5 but I've tried with 2.1 and get the same. Driver problem?
    Attached Images Attached Images

  5. #5
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation problem(Excel/Access) (2k)

    <hr> are you on win2k<hr>

    yes

  6. #6
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation problem(Excel/Access) (2k)

    Brooke,

    I am using Excel 2000, win98, but I think Rory is right, it has to do with your pathtodatabase string. Check that one or try out another one.

  7. #7
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Automation problem(Excel/Access) (2k)

    That's why then - it's a very annoying 'feature'. Check <A target="_blank" HREF=http://www.wopr.com/cgi-bin/w3t/showflat.pl?Cat=&Board=acc&Number=29891&page=&view =&sb=&o=&vc=1>this</A> post for a solution - it involves a small registry hack though.
    Hope that helps.
    (Incidentally, is there a reason for using the DBase driver rather than the mdb one?)
    Regards,
    Rory

    Microsoft MVP - Excel

  8. #8
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation problem(Excel/Access) (2k)

    no reason at all. I know no better!

    I changed your code to match my string and got this:
    Attached Images Attached Images

  9. #9
    4 Star Lounger
    Join Date
    Jan 2001
    Location
    Kortrijk, Belgium
    Posts
    571
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation problem(Excel/Access) (2k)

    Maybe you can try

    .ConnectionString = "Provider=microsoft.jet.oledb.4.0;" + _
    "Data Source=" & PathToDatabase

  10. #10
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: Automation problem(Excel/Access) (2k)

    Can you post the amended code that produced that error?
    Regards,
    Rory

    Microsoft MVP - Excel

  11. #11
    5 Star Lounger
    Join Date
    Dec 2000
    Location
    Reading/Swindon, Berkshire, United Kingdom
    Posts
    664
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Automation problem(Excel/Access) (2k)

    no need. I had a typo on the query name. Sorry! So I've changed that and it runs fine. and Hans, your suggestion on the first batch of code means that is running fine as well. Thank you both very much for your time.... You have helped me out of a hole!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •