Convert MS Access database to web based DB (1 Viewer)

[PhilC]

Hello Guys,

I have created an Access database which runs cribbage league results. I now want to convert this so I can add it to our existing website and allow members to enter their own results.. I think I know how to transform the tables to SQL. But the problem I have is creating a new front end that most of my inexperienced members could cope with. I just do not have the knowledge to venture that far.

I would enthusiastically welcome any ideas or suggestions you may have. Many thanks in anticipation.

 

[plog]

Hire someone, buy an off the shelf solution. Front end web development is the worst.

You could spend a week learning html and css, then another week learning a web programming language. Then with that knowledge you could spend a third week creating a fully functional site that looks like dog crap. Again, it would work, but it would not be anything anyone in 2022 would expect to use or not stop complaining about.

 

[isladogs]

Only 3 weeks! That's very optimistic!

 

[GPGeorge]

Hello Guys,

I have created an Access database which runs cribbage league results. I now want to convert this so I can add it to our existing website and allow members to enter their own results.. I think I know how to transform the tables to SQL. But the problem I have is creating a new front end that most of my inexperienced members could cope with. I just do not have the knowledge to venture that far.

I would enthusiastically welcome any ideas or suggestions you may have. Many thanks in anticipation.

You have options. Depending on what is most important to your group as well as your budget, one of them might be most appropriate.

You have a website already, and now you simply want to publish results. Not hard. Allowing members to enter their own results. That's a whole different ballgame.

Actually, I might consider one of three options.

• Create new asp.net pages. As someone pointed out you'll learn several new technologies and coding languages. 3 weeks? Unlikely. Maybe in 3 months--full-time, not as a part-time project undertaken on behalf of the club. I assume you have other responsibilities. Security alone is going to require a serious investment of your time.
• Deploy a run time version of Access with a copy of the current Access Front End accdb to each of your users. Move the data into SQL Server, of course, so they can all reach the database and you can create html pages to display the results. I'm guessing a couple of months, again, more or less full-time.
• Migrate the data into SQL Server and create a PowerApps interface to that data. This would require an MS 365 account to host the PowerApps app itself and provide credentials for users to use it. This is probably on the higher end cost-wise, although I think you could get a workable PowerApps app up more quickly than the other options.

All of these involve serious commitment to learning new technologies. If that's not in the cards, a consultant paid for by your group could be considered an option.

 

[Pat Hartman]

You can divide and conquer. Rather than using the web to collect data from the participants, you can have them send you emails. You can give them a format to follow. If you go this route, the easiest way is to use a new email address used only for this purpose. Your Access app can look at the email folder a couple of times each day and process any pending updates. you would send errors back via email also. Then every night, refresh the standings report to include all the emails you got that day.

Getting Access to read and write emails is child's play compared to learning web technologies and all the security that implies.

You also have to remember the inside/outside the firewall aspect of allowing users to update data on line that you are using off line with Access. This is probably a personal project but once you allow a webpage outside your firewall access to your database located inside the firewall, you open up a seriously dangerous can of worms.

 

[CJ_London]

perhaps you would like to take a look at what is offered here

Call for thoughts about Access to Web

Hi everyone Pls let me introduce myself. I'm Dean, an IT professional with more than 25 years in the industry. What I'm after is help from this community. But, it's controversial, because some might think like an intruder or something :) So, what we made is an automated Access to Web...

www.access-programmers.co.uk

I've not tried it so I'm not recommending it, but would be interested in your thoughts as to whether it is a way you would want to go

 

[Pat Hartman]

I wouldn't recommend this site. They don't convert anything. You just upload your data and you can access that through their interface. "Conversion" implies functionality will be similar. Not even close.

 

[Pat Hartman]

At one point, there was a tool out there that converted Access forms to VB6. That would actually not be impossible. Even VB.Net could be done because VBA is "convertable" to VB.Net. If you've ever looked at the output when you use Export to Text, you'll see what a conversion tool gets to work with.

 

[PhilC]

Hello Pat,

I like your concept in your first response. But how on Earth do I get Access to READ an incoming email? I know how to send one. I have used CDO and it works perfectly, but I have never worked out how to 'read' an incoming email. Can you advise please, as this seems to provide an ideal solution.

Many thanks

 

[Pat Hartman]

Open the get external data dialog and link to tables from Outlook. The best way to do this is to use a specific email address for this job only. The other problem is to standardize the input. It won't be too hard if you have only a few fields. Probably the best solution is to use fields and delimiters. Maybe:
Fld1(your answer here)
Fld2(your answer here)
Fld3(your answer here)
This should be easy enough to parse with VBA. You have three fields, look for the fieldname followed by the open paren, The rows are separated by vbCrLf characters
Or, you can use only one delimiter such as the colon:
fld1:yournanser here:fld2:your answer here:fld3:youranswer here

This is actually easier since you can use split with ":" as the delimiter. as you loop through the array, you can ignore the field names and just pick the 2nd, 4th, and 6th items and assume they hold the right data.

 

[Auntiejack56]

Before I make a suggestion, are the cribbage results always in the form "Suzie Bloggs 12/5+106"? And does (for example) Suzie Bloggs send in her results independently? Or does the host or team captain send in results for the top, say 5, people? What about location of the game or competition - does the result say "Gerrards Cross Wednesday teams: 1st Suzie Bloggs 12/5+105, Second Jack Frost 10/5+87 ..." etc? And presumably every Wednesday teams event doesn't overwrite the previous results, so you are keeping historical (dated) information as well. The more information that you want to capture, the more challenging this is going to be.
You noted that you already have an Access database, so I'm also wondering whether your proposed website is just for capturing the results, or do you want to publish the results to the website as well?
I know I sound like a pain, but answers to these questions will inform the answers that people provide. Simple parsing of an email, for example, might become quite complicated where the amount of data is larger.
I haven't played crib since my dad taught me all those years ago, interested to hear your answers.
Jack