Dilemma stay with out of date sql or back to access (1 Viewer)

[Dazzy]

Hey Folks

Hope you guys can give me a bit of advice here, a few years ago a developer sold the organisation an SQL server Database. This database is now seriously out of date and is harming our growth prospects. The developer had been contacted to make changes to it but has since vanished.

It's been a while since I last looked at Access but I know it will come back quickly if I go this route. Our organisation is small, 8 staff where only 2 or 3 would be accessing the database at the same time.

Since last year we updated our internal documents to gather marketing data and this data is going to waste as we have no way to record it in our client database.

Another big headache with the SQL database is the developer left it that the reports are throwaway .csv files and they are beyond the office staff even how to do a simple task like mail merge which as you can imagine leaves everything inefficient.

My question here is would I be better designing a new DB in access to gather all this data and somehow import the data from the .csv reports generated by the SQL database so we're not starting over or stick with SQL and hire a new developer which we cant really justify the cost in these hard times?

I have attached a sample database which contains the imported csv files structure (import tables) and some new tables I would use in an Access DB.

Queries have always been my weak point to say the lease, could someone please take a look and see if it is possible to import the data from the csv tables to the new ones and if so provide me a sample of a query that could do it?

Many thanks in advance for any insights you can provide.

 

[spikepl]

How many is "a few years ago" ? Because the world doesn't stand still and there are new commercial or open source codes and services out there ...

Identify ALL your options, based on situation now, and 1 and 2 years from now. Developing yourself is not free. Development by other party is not free either - on top of payment for developer time, transmission of domain knowledge, requirements and testing gobbles up your time too.

For each option estimate expected pros & cons, generated income and necessary expense. Remember that having spent X funds already is irrelevant, because most of it is irrecoverable sunk cost. The interesting expenditure (time & money), for each option, is what it takes from now on and to where you want to be. Compare and decide.

 

[Dazzy]

Hey, it was in the region of 5 years ago so I would need to double check this but I'd assume that the cost has been written off by this stage.

If I could determine an easy way to import the data at least it would leave me with more options to ponder.

Just as an example for one of the reports I specified in the revisions list the developer had quoted £800, but I was able to reproduce the same effect using a master and child forms and linking filter boxes in about an hour in Access. My rate of pay is sub £10 per hour so on that metric could spend 80 hours on something and get a lot more done than one filterable report.

 

[spikepl]

I have no clue about your business. The reason I mentioned the things I did is that I have seen brainless development devouring many resources. By the rate of pay, indeed cheaper than outside development or some off-the-shelf stuff. But effect on business a total disaster, when people were not doing the jobs they were hired to do but were mired in progress meetings, coding, debugging, error reporting and testing. My point is that rate of pay is not necessarily the only relevant metric.

 

[Lightwave]

Its unclear from your description exactly what the set up is.

What version of SQL Server are you on?
I take it that it's SQL Server express?
Does anyone have sql server management studio?
Could you get SQL Server Management Studio?

What's the front end written in I take it it isn't access?

SQL Server Management Studio isn't terrible and is reasonably familiar if you are good with access.

M

 

[Dazzy]

Hi

The front end is written in ASP, html and css, I know html and css but nothing about ASP.

I believe it's SQL Server 2008 but which variant I have no idea. No the dev nor us has access to the management studio.

Thanks guys, amazing what a clear head in the morning can achieve, I can now easily import the data that is exposed in the csv reports, certain data like Next of Kin details don't get exported so these cant get imported.

 

[Lightwave]

Ok thanks for that.

Well I would suggest you maybe try and find out which version of SQL Server you are on. Then load management studio on a client computer and try and link it up to your server (you will need network and database permissions for this so you may need to get that from central IT - you also need to know where it is!!). That way you at least have an opportunity to see and alter the back end.

After that you can choose.

Keep the back end in SQL Server (which is what I would do) but do all front end development in Access

or

Move the whole lot to Access and manage the back end in access and do front end development in Access.

Note if you keep the backend in SQL Server chances are all your ASP.NET CSS will still work. That may be of value I don't know

 

[Dazzy]

Hi

Thanks again. I think we will end up going on all Access as at least then it can be managed inhouse. We did the same with our web services this year, and I now look after a Wordpress company site, developing a VLE with Moodle and deploying Office 365. (I don't know anything about access web apps yet, but in time I hope to surface info through a web app in our team site).

I now have about 90% of the queries done to update the data to the new format when I do the final import. Some data doesn't get exported with the CSV files, mostly around next of kin details so this will have to be added back over time. Similarly some wont come over well to the new structure so I am surfacing this in read only fields and in time these fields will disappear.

The office staff are having to use about 15 excel spreadsheets in addition to the SQL database to do their daily jobs, the plan is to house all this data in Access rather than duplicating across multiple spreadsheets. Then surface front ends that will make it feel familiar to the staff during the transition.

In the end there will be multiple front ends in a bid to completely integrate the organisation from tracking cold calls, right though to the stage they become a client and then following up with marketing promotions after they finish.

I want to build logic into the DB too and this is where I will come unstuck lol for instance when a client completes a course with us, this is automatically appended to their previous education history, which can then be exported to a CV.

Another area is if a cold prospect becomes a client, it auto updates a field to reflect this and then updates them to prospective client if they expressed an interest in returning or former student. If they come this cycle starts again.

Gary

 

[Lightwave]

Good luck

From your descriptions you would appear to have a clear plan of attack and a clear vision.

That makes things a lot easier.

 

[Pat Hartman]

You could leave the data in the SQL Server database and build an Access FE. That would eliminate any conversion and allow you to keep the ASP app "working" until the Access FE is complete. Access is a fine FE for ANY relational database that offers ODBC connections. Working with tables linked to SQL Server is pretty much the same as working with tables linked to Jet or ACE. You do have to think a little differently and base your forms on queries with selection criteria that selects only the specific rows and columns the user wants at this time. So, either a pre-form where lots of criteria can be specified to bring up a list and then double-clicking on one to bring up your maintenance form or if the criteria is simple enough, you can just put the text boxes and combos in the form's header section. It is not necessary to use pass-through queries or stored procedures except in rare cases. Start with using Access queries for everything (Access makes every effort to "pass-through" every query anyway) and only look into other options if the Access query is too slow.