Sharing An Access Database Options (1 Viewer)

[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.

 

[Pat Hartman]

Sharepoint won't work for you unless you completely rewrite the application. The best solution will be a hosted SQL Server database. This should run less than $10 a month and support more than three people easily. Depending on how you designed the application, you will have 30 minutes of time to run the upsizing wizard and verify that everything still works or several hours + of time to modify the forms and queries so that they conform to good client/server practices.

Start by downloading the free SQL Server express product from the Microsoft website and installing it on your computer.

Go through your schema and make sure that every table has a primary key and all the relationships are defined. Also make sure that field lengths and types are consistant. If you join State in your Address table to State in the lookup table and one is defined as 50 characters because you forgot to change the default and the other is defined as 2 characters, Access will accept it but SQL Server will not. Jet/ACE are somewhat more flexibile than SQL Server so something that Access accepted may be rejected by SQL Server. If you have badly named tables and columns, it will be easier if you simply fix them now while you can still make use of Name AutoCorrect. Once you upsize, you'll have to do it all by hand or with a third-party tool if you decide I was right and end up doing it later. "Bad" names contain punctuation and embedded spaces. Good names contain only a-z, A-Z, 0-9, and the underscore. Names should be CamelCase or use The_Underscore to separate words to enhance readibility.
Once you have cleaned up the tables and relationships and indexes, you will then be able to run the upsizing wizard (make sure you backed up your database first) to see if you get any errors.

Once you've gotten past the upsizing wizard, you need to address your forms. Forms should NEVER be bound to tables or to queries without selection criteria. Access opens a form as soon as a few records are available to populate its RecordSource. It then sits there quietly sucking all the rows in the table down from the server. In a WAN especially, this is very slow and takes way too much bandwidth so you need to modify the forms to give the users search options that limit the rows selected and brought down from the server.

If you are using DAO and/or ADO in VBA, you'll need to add the dbSeeChanges option to your .execute and .OpenRecordset methods whenever a table in the query includes an autonumber.

Do some reading on optimizing Access for client/server to see more hints.

 

[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.

 

[Pat Hartman]

Here's the conversation that Dave and I had off-line.

Originally Posted by gemma-the-husky
pat

can you clarify some comments you made about this topic, and provide some more information
it sounds very useful, but i am not clear on some things

1. what sort of providers offer this sort of service?
2. is the front end on the server as well, or just the back end?
3. how many users can use the data simultaneously
4. how secure is the data in such environments
5. how does a user connect to it
6. how do you attach printers, and local pc's in order to save data locally
7. why 30 minutes to design the database?
8. do users still need an access front end?

etc.

maybe a general "sticky" would be very useful

thanks

Dave,

1. GoDaddy and many others
2. The FE is local as it is with any LAN installation.
3. Lots. The provider will charge more for high volume access.
4. I don't know
5. The tables are linked to a url address instead of a server on the LAN
6. The FE is local and works EXACTLY as if it were connected to SQL Server on the LAN
7. Because if you follow good design techniques when you create your Jet/ACE tables, all you have to do is to run the SQL Server upsizing wizard to create the SQL Server database on your local PC (use SQL Server Express if you don't have a network installation you can use). Then verify the results. If you have no errors, you're good to go.
8. Nothing in the FE changes. It is still an Access FE linked to a server in the sky rather than down the hall.

The most annoying issue is I haven't found a provider that makes it easy to transfer databases. For production applications, this isn't a problem, you transfer the database once when you create it. The provider installs it in their SQL Server environment. From that point on, you either use their web interface to modify the schema or some providers allow you to connect via SQL Server Management Studio and use that (my preference because I hate web interfaces). For testing purposes, I want an easier method of creating/deleting databases. None of the providers allow you to restore a .bak using Management Studio or point to their server to upsize directly. You have to upsize locally, then transfer the .bak to the provider for installation.

I haven't done this enough to post a sticky but posting this back to the post that prompted the questions will be helpful.
Pat

]thanks for this info. i take it this sort of set up can be used in situations where a WAN would not give adequate performance. I will be having a look into this.

It is the same as a WAN. Jet/ACE do not work well on WANs because they move too much data over the network. You need SQL Server or another RDBMS to make a WAN feasible
Pat

 

[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?

 

[Pat Hartman]

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?

1. Azure is a crippled version of SQL Server. If it has the features you need, it will be fine.
2. No. Access queries written against SQL Server, Oracle, DB2, etc. are automatically "passed-through" to the server for processing unless the query contains some element that cannot be translated such as a VBA or UDF function in the where clause. The query is processed on the server and the requested recordset is returned. If you ask for 1 record, that's what gets sent back. Jet/ACE are quite different and that's why they are unsuitable for use on a WAN unless you have verrrry small tables and some patience. With Jet/ACE all the processing is done on the client so large blocks of data must be downloaded from the BE database and the joins are done locally.

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?

GoDaddy was one of the providers I tested. I mentioned them because they are a familiar name. GoDaddy as well as most providers are geared up to host websites. If you ask them to only host the database, they charge less but it is invonvenient to make bulk changes. There are features in Management Studio that you won't be able to use. Most providers don't require long term contracts so you can sign up and test for a month. If you don't like the service, cancel it and try someone else.

 

[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.

 

[Pat Hartman]

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

Acces, like other relational database systems, uses its own variant of SQL. A query that you build with the QBE (Access SQL Syntax) will not run if you cut and paste it into SQL Server Management Studio. The syntax of SQL statements in all SQL implementations is recognizable but ever so slightly different from one RDBMS to another. Plus features outside the core defined by the standards committee, are at the discretion of the RDBMS. When you create pass-through queries, you code them in the syntax of the target RDBMS so if you want the query to run in SQL Server, you use that syntax. If you want it to run in DB2, you use that syntax. Part of the overhead involved in running an "Access" query is having the query engine translate the Access SQL syntax to that of the target RDBMS. So, pass-through queries are passed through untouched but Access queries are translated to the target syntax and then passed through. In either case, the query runs on the server and returns a recordset.

 

[Pat Hartman]

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?

No. A back end database in the cloud is EXACTLY the same as a back-end database on a local server. It just takes longer to pass data because a WAN connection is substantially slower than a LAN connection. The Access FE still uses linked tables and the FE still resides locally on the user's machine. However, the user now requires internet access to get to the BE. The only differences is that when you link the tables, the server name will be a url.

 

[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.