troubleshooting Question

Excel to Access 2007 Connection String

Avatar of uwlchemist
uwlchemist asked on
Microsoft AccessMicrosoft ExcelMicrosoft 365 Enterprise
6 Comments2 Solutions1094 ViewsLast Modified:
What is the correct connection string for downloading information from a split Access 2007 database (on a network) into Excel 2007 when the users computer does not have Access installed?  I need Excel to download information from this database on a regular basis.  The code (See below) worked perfectly on every computer before I split the database...now it only works on computers that have Access installed.  Computer that do not have access installed show the error "Run-time error '-2147467259 (80004005)':  Could not find installable ISAM."  I have looked at both the registry and the code, and can't seem to correct the issue.  Any and all ideas are welcome
Const strConnectionString As String = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" & _
        strDatabaseLocation & ";Jet OLEDB:Database Password=" & Password


    Dim Connection As ADODB.Connection
    Dim RecordSet As ADODB.RecordSet
    
    Set Connection = New ADODB.Connection
    Set RecordSet = New ADODB.RecordSet
     
    Connection.Open strConnectionString
    RecordSet.Open strSql, Connection, adOpenStatic, adLockOptimistic
     
    rngTargetSheet.Activate
    rngTargetSheet.Name = strSheetName
    rngTargetRange.CopyFromRecordset RecordSet
    
    Set Connection = Nothing
    Set RecordSet = Nothing

Open in new window

Error-Message.bmp
ASKER CERTIFIED SOLUTION
Jan Karel Pieterse
Excel and VBA Expert

Our community of experts have been thoroughly vetted for their expertise and industry experience.

Join our community to see this answer!
Unlock 2 Answers and 6 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 6 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros