CODEWITHSUNDEEP
excelvba
Michiel Soede
Michiel Soede

Reputation: 55

Connect from Excel to Access database on internet server

I hope someone is able to help me out with the following. Currently I have an excel based tool that is able to retrieve and upload data (via ADO) to an Access database. This works perfectly fine, but now I need to use it on a server that is to be approached via Internet. Running the macro does not work any more where it is giving me a run time error: 'Your network access was interrupted'. I use the following connection string:

pad = ActiveWorkbook.Path xdatabase = pad & "\Rolling forecast Database.accdb" stDB = xdatabase stConn = "Provider=Microsoft.ACE.OLEDB.12.0;" _ & "Data Source=" & stDB & ";"

Although I see that people encounter the same problem I am not sure what it is that causes the macro not to function properly any more. It has to do with the fact that the server is probably not local? Any solutions for this problem?

Grateful for your swift response,

Cheers Michiel

Upvotes: 2

Views: 791

Answers (2)

Michiel Soede
Michiel Soede

Reputation: 55

Thanks for your reaction guys. I solved it by using a local server! Cheers

Upvotes: 0

stuzor
stuzor

Reputation: 2402

You could use HiveLink (https://hivelink.io) to do this..

It would basically turn your spreadsheet into a live service and you would need a computer running all the time with have your original spreadsheet. HiveLink would help you create a "lightweight" spreadsheet that your users can use anywhere, which doesn't have any macros or calculations. A person using it would authenticate themselves, enter their input data, press a macro button and the inputs would be sent to your "master" spreadsheet. HiveLink would run the macro you choose in the master spreadsheet, which could be your Access database upload or whatever, then it would send back any results you want your user to have. The connection string would stay only in your master spreadsheet. End users don't need to install anything, but it only works in Excel (2010+) for Windows, and they would need to enable macros for it to work.

Upvotes: 1

Related Questions