excel link to access

J f
J f used Ask the Experts™
on
how can i integrate excel with access? For example, We have a database file (.accdb) on a sharepoint, that I would like to pull data from into Excel systematically (call it once a day). I then want to use that data and run various analytics off of it/generate reports.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Could you not create a link to the Excel file and then run queries, VBA, ...?
J f

Author

Commented:
Yes, I would like to do that. Are you able to help direct me in doing so?
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
External Data -> Excel
Then follow the dialog.
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

J f

Author

Commented:
right, so I tried that. I have the access file on SharePoint, and I have the Sharepoint drive mapped on my PC. So when I try and copy the access file locally to my PC, I get an error (see attached). I get the same error when I try and open the file via sharepoint that is mapped on my drive on my PC.

I am able to save, or open the file when I access Sharepoint via a web browser. Any ideas?
Capture-sharepoint.PNG
NorieAnalyst Assistant

Commented:
Can you not link to the file on Sharepoint instead of copying it to a local drive?

Have you tried, from Excel, going to Data>Get Data>From Database>From Access Database?
J f

Author

Commented:
So I cannot import when selecting the database file from sharepoint via a mapped network drive.

When I try to import directly from the Sharepoint webpage, I can import it - but what I'm nervous about it, if I make changes here, I don't want it to make any changes to the master file.

That is why I wanted to try and pull down the latest file from the sharepoint locally, and work off of that data.

Once it's imported, there is various things I'd like to do. I'd also like this all to be done via vba.
Distinguished Expert 2017

Commented:
Access cannot be shared on Sharepoint so I'm really not sure what you are doing.  Access cannot be shared using any cloud drive.  However, if you are the only user of the application, you should be able to open the Access database while it is on the SharePoint drive and work with it as if it were on your local drive.  If you don't copy the database to your local drive before you open it, any imports or changes you make will affect the copy on SharePoint if that is what you are referring to as the master.

Are you saying that after you link to the spreadsheet and do some analysis, you are willing to loose your work?  You don't need the imported data and you don't need to save the queries you used?

Attempting to share an Access database using SharePoint or any Cloud drive is very dangerous since anything you modify will overlay any changes made by any other currently active user if you save after he saves.  People can only use Access SERIALLY this way.  User1 has exclusive control and saves his changes.  Then User2 can open the app and do what he wants and save his changes.  User3 can then do his thing.  You CANNOT have multiple users opening the database at the same time.  They will just clobber each other's work.
J f

Author

Commented:
only 1 person edits the database on the sharepoint. everyone else just view as read-only.

What I'm trying to do is copy it, and save it locally so that i can import to excel and run vba queries off the data.

I'm trying to figure out the best way to systematically to copy this over locally.
J f

Author

Commented:
I do not want to make any changes to the master file. Just looking to copy it locally, and work the data via excel.
Benjamin LuSAP/ERP Data Player

Commented:
Hi,

Please try to use Pentaho Data Integration(PDI) to do data integration(Java base).
It's free for use,you download it and use without installation, it can handle file/data, and easy to learn for use:
And, it's very easy to schedule one task to download it based on your requirement automatically .(everyday,7:00am,every Firday 9:00...)
PDI
Untitled.jpg
Distinguished Expert 2017

Commented:
If you don't want to modify the master copy of the database, then download the database and do your work locally.  Don't upload the modified copy.

The error message you were getting is not from Access.  It is coming from Windows or SharePoint.  Please ask your SharePoint administrator how to resolve it.
Benjamin LuSAP/ERP Data Player
Commented:
Also, please try to use ADO to copy the data ::
Dim MDB As New ADODB.Connection,rs As New ADODB.Recordset, cnn As String,MDBFile as String
Dim TName as String,K as integer,I as Integer
MDBFile ="C....\.accdb" 'Your file 
TName="" 'Your Table
cnn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & MDBFile & ";Jet OLEDB:Database Password=" 
MDB.Open cnn
rs.Open "Select * from " & TName, MDB, 1, adLockReadOnly

'Copy data to sheet1
If rs.RecordCount>0 then
Sheet1.Cells.ClearContents
rs.movelast

K = rs.Fields.Count
for i =1 to K
Sheet1.Cells(1, i) = rs.Fields(i-1).Name
next i

Sheet1.Range("A2").CopyFromRecordset rs
end if
rs.close
set MDB = nothing

Open in new window


Please refer to ADO ActiveX
Untitled.jpg
J f

Author

Commented:
Benjamin Lu - this is the route i'm trying to go for. Using excel to retrieve data from the database and copy into excel to then work off of it.

I tried what you suggested above, i'm getting a run-time error
unrecognized database format: 'H:\test-jf.accdb'.
Capture.PNG
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
For accdb format you need to use

Microsoft.ACE.OLEDB.12.0;

as your provider.

https://www.connectionstrings.com/ace-oledb-12-0/with-database-password/
J f

Author

Commented:
that works, thank you.

I'm able to connect and import, however it appears it doesn't import the full table, only 1 line? It's not even the first line, its just a random one within the table.

How can I correct this to copy over everything from that table?

MDB.Open cnn
rs.Open "Select * from " & TName, MDB, 1, adLockReadOnly

'Copy data to sheet2
If rs.RecordCount > 0 Then
Sheet2.Cells.ClearContents
rs.movelast

K = rs.Fields.Count
For I = 1 To K
Sheet2.Cells(1, I) = rs.Fields(I - 1).Name
Next I

Sheet2.Range("A2").CopyFromRecordset rs
End If
rs.Close
Set MDB = Nothing
End Sub

Open in new window

President / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Commented:
Why are you rs.MoveLast?  Try commenting that out and it should work properly.
J f

Author

Commented:
that did it, thank you everyone!
J f

Author

Commented:
If I wanted to import a different table table from the same access file (but into another sheet of this workbook), how can i go about adding that into my code?
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018

Commented:
Switch your
rs.Open "Select * from " & TName, MDB, 1, adLockReadOnly
to another TName and then change your Sheet2 variable to point to a different Excel sheet.
J f

Author

Commented:
I somehow messed something up and get a run-time error on:
MDB.Open cnn

I can't even get it to work normally, after I tried to unwind the changes I made to add another table.
Capture.PNG
Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018
Are you providing the password?
J f

Author

Commented:
There is no password on it.
Benjamin LuSAP/ERP Data Player
Please verify the file path exists or not.
J f

Author

Commented:
Hi, yes the path does exist. I'm not sure why it's failing. Any ideas?
Benjamin LuSAP/ERP Data Player
Please compare it with your successful one, and you will find the difference.
J f

Author

Commented:
Benjamin, I can't get it to work. Initially, yes it worked. Then I started to try and pull in table reading what Daniel menitioned. It failed, then tried to revert back and hasn't worked since. I even tried to create an entire new project, re-added the activex references, and still can't get it to work! It keeps highlighting that area (MDB.Open cnn). I'm happy to create another thread for credit, if someone can please help me. It's driving me crazy trying to figure this out.

Sub MasterOrderDB()
Dim MDB As New ADODB.Connection, rs As New ADODB.Recordset, cnn As String, MDBFile As String
Dim TName As String, K As Integer, I As Integer
MDBFile = "H:\COMMS2014.accdb" 'Database File
TName = "MasterOrder" 'MasterOrder table
cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MDBFile & ";Jet OLEDB:Database Password=" 'test with new provider

MDB.Open cnn
rs.Open "Select * from " & TName, MDB, 1, adLockReadOnly

'Copy data to sheet2
If rs.RecordCount > 0 Then
Sheet2.Cells.ClearContents
'rs.movelast

K = rs.Fields.Count
For I = 1 To K
Sheet2.Cells(1, I) = rs.Fields(I - 1).Name
Next I

Sheet2.Range("A2").CopyFromRecordset rs
End If
rs.Close
Set MDB = Nothing
End Sub

Open in new window

Daniel PineaultPresident / Owner CARDA Consultants Inc.
Distinguished Expert 2018
I have tested the following and it works
Sub a()
    Dim MDB                   As New ADODB.Connection
    Dim rs                    As New ADODB.Recordset
    Dim cnn                   As String
    Dim K                     As Integer
    Dim I                     As Integer
    Const MDBFile             As String = "H:\COMMS2014.accdb"    'Database File
    Const TName               As String = "MasterOrder"    'MasterOrder table

    cnn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & MDBFile & ";Jet OLEDB:Database Password="     'test with new provider
    MDB.Open cnn
    rs.Open "SELECT * FROM " & TName, MDB, adOpenKeyset, adLockReadOnly
    With rs
        If Not .EOF And Not .BOF Then
            Debug.Print .RecordCount
            'Your code goes here ...
        End If
    End With
    rs.Close
    Set rs = Nothing
End Sub

Open in new window


You've double checked the path and filename, the table name?
You've ensured your reference libraries are properly set?
You're code compiles without errors?


You could also try the following (changed connection string since you don't need the password)
Sub a()
    Dim MDB                   As New ADODB.Connection
    Dim rs                    As New ADODB.Recordset
    Dim cnn                   As String
    Dim K                     As Integer
    Dim I                     As Integer
    Const MDBFile             As String = "H:\COMMS2014.accdb"    'Database File
    Const TName               As String = "MasterOrder"    'MasterOrder table

    cnn = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & MDBFile & ";" & _
          "Persist Security Info=False;"
    MDB.Open cnn
    rs.Open "SELECT * FROM " & TName, MDB, adOpenKeyset, adLockReadOnly
    With rs
        If Not .EOF And Not .BOF Then
            Debug.Print .RecordCount
            'Your code goes here ...
        End If
    End With
    rs.Close
    Set rs = Nothing
End Sub

Open in new window

J f

Author

Commented:
Thanks Daniel.. I tried again. I even created a new test database with the same tablename and still errors on the line:
MDB.Open cnn

I tried with the above code you provided as well. Can't seem to get it to work.

Could it be the references? See attached.

Any other ideas as to why its failing?
refs.PNG
Benjamin LuSAP/ERP Data Player
What's the OS/office version/MS access version installed in your PC?

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial