Sharing An Access Database Options

[twoplustwo]

Hi all.

I have a database that needs to be used by two people in an office location and one person remotely. The office has no local network as far as I am aware... What options do I have? Ideally a solution will entail little to no changes to the db. We are happy to pay for an online shared workspace etc.

I have been reading about Share Point but supposedly any VBA is redundant. The database is written is Access 2007 fwiw.

Thanks for any help.

 

[twoplustwo]

Morning Pat, thanks for the detailed answer.

The database is fully normalised and I only open a handful of recordsets so updating those aspects should be ok. I've a series of combo boxes to search the database so that would limit the processing required by the database. Names are CamelCase and contain no dodgy characters/conventions. Based on your pointers prior to starting we seem to be in a reasonable position to start! I'll read into the client/server optimisation.

Thanks a lot.

 

[Lightwave]

You should look into terminal services as well.

That would require no alteration of the database and is probably the easiest option. It could be used for other applications as well.

There are others but here's a group that provide this service

https://www.gotomypc.com/tr/google_...semuk?Target=/mm/g25semuk.tmpl&Location=en_GB

and another market leader LogMeIn

https://secure.logmein.com/welcome/...75558&ef_id=nwdN6kXQwnYAAMFz:20120505094501:s

 

[gemma-the-husky]

Pat

I have sent you a pm with regard to the suggestion about SQL Server Hosting.

do you think you could start a general thread about this, as it sounds like it would be of great help to many users.

 

[lovrandy]

you can use logmein.com to share your pc with others.

 

[lovrandy]

you can also use teamviewer.com to share pc's.

 

[lovrandy]

im not great with access so just learning to use really. sorry couldnt be of more help.

 

[twoplustwo]

Hi guys, thanks for the discussion. Really appreciated.

This is definitely something I am keen to resolve.

 

[twoplustwo]

Hey Pat, thanks a lot for the additional detail.

I am downloading SQL Server Express (with tools...): http://www.microsoft.com/sqlserver/en/us/editions/2012-editions/express.aspx

Is it easy enough to google a third party that will host the data? I will look into GoDaddy.

In some order my plan of attack will be:

- Get SQL Server installed, take a copy of the db
- Make the small code changes that pertain to recordsets and any others I can research
- Upload the tables (?)
- Link the forms/VBA and test functionality

How would I connect the FE (I presume this could be an Access db with no tables etc.) to the SQL Server db?

Again, thanks a lot for your advice.

 

[spikepl]

@Pat

1. How about Azure?
2. Shouldn't the queries be rewritten, to use pass-through or stored procedures/views , so that the heavy lifting is done on the SQL server, rather than the locally run Access engine dragging all the data across the WAN for processing? At least this is my understanding - is it correct? Or is the upgrade thingy smarter than that?

 

[twoplustwo]

I've been poking around the net. Am I able to host the db on GoDaddy and allow it to be updated from that location?

 

[spikepl]

@Pat

I am confused now. You wrote

Access queries written against SQL Server, Oracle, DB2etc. are automatically "passed-through"

What exactly is "Access queries written against SQL Server" ?

Say I have an Acccess FE with some linked tables in an Access backend and a query. One day I migrate the backend to SQL server. Is the query now run on the SQL server automatically?

If that is the case, what is the point of being able to create specific pass-through queries, eg., as described here: http://support.microsoft.com/kb/303968 ? Or are the queries converted to pass-through by the migration tool?

 

[twoplustwo]

Pat, if I understand you correctly I can host the db on the site and enable users to open forms/update data from that location? No front end/data connections?

Thanks again for your help.

 

[Adam Caramon]

Great information Pat. It is very much appreciated.

 

[twoplustwo]

Hi Pat. Right... I have uploaded my Access db to the site. I am going to try linking the tables as you suggested via the URL...

Thanks again.

 

[twoplustwo]

Hrm what type of link do I need to select to link to the tables in the cloud?

 

[twoplustwo]

Hmm absolute hosting path perhaps. (D:\Hosting\9328479\html) The DSN is

ns05.domaincontrol.com
What type of link am I attempting? I am unsure with the database now being (I think...) located on the server via FTP.

 

[twoplustwo]

I was going to give it a try... I can try the upsizing wizard in Access to generate the SQL Server database.

Edit: I'm dling SQL Server 2012 at the minute.