Need write SQL

[Dick7Access]

I know next to nothing about SQL, SQL server. I have a db I would like to have hosted so my wife could access it at the rehab hospital she is at. I have found some reasonable priced host. My question? Before I can have it hosted do I have to write some sql code?

 

[GinaWhipp]

When you say hosted are you considering an Access Web App? Because then you're talking Data Macros unless you are doing a Hybrid Database.

 

[Dick7Access]

When you say hosted are you considering an Access Web App? Because then you're talking Data Macros unless you are doing a Hybrid Database.

Thanks for answering.

I have a small db (14,000) records. I would like to get a company to host it so that my wife (In rehab hospital) could do data entry. While I am on the road doing meetings I could access it from whereever I have WiFi. (most motels)

Now if I do any data enter or code changes it doesn't sync with her copy, and visa versa.

I am under the impression that sql is necessary, and I haven't got the time nor inclination to start studying SQL.
Dick

 

[GinaWhipp]

Hmm, SQL not necessary or required but again changes might be needed depending on what you want to do (and of course, the database is split), i.e.

VPN - no changes
Access Web App - changes needed
Remote Desktop - no changes

So, it comes down to what you want to do. That said, VBA does not work in an Access Web App so that might not be an option unless you want to do a rewrite BUT the database could be accessed from anywhere you have internet access. If they have a VPN then the database could be loaded there and you or your wife can access from outside the location.

 

[plog]

I think you are asking misleading questions. In your mind you've already taken 3 steps toward where you want to get to and you are asking about how to get to another intermediate point. However, you never tell us your orginal starting point nor your ultimate destination. So we aren't sure what path you are on, nor where you are going.

Quit focusing on SQL. Tell us what you currently have and what you want to accomplish.

From what I have heard so far Google Docs is the best solution. It allows you to colloborate on documents. Put your data in a spreadsheet and you can both work on it online.

If that won't work, tell me exactly why. Tell me the features it doesn't have but needs to. Give a better description of your current situation and what you want to end up with.

 

[Dick7Access]

I think you are asking misleading questions. In your mind you've already taken 3 steps toward where you want to get to and you are asking about how to get to another intermediate point. However, you never tell us your orginal starting point nor your ultimate destination. So we aren't sure what path you are on, nor where you are going.

Quit focusing on SQL. Tell us what you currently have and what you want to accomplish.

From what I have heard so far Google Docs is the best solution. It allows you to colloborate on documents. Put your data in a spreadsheet and you can both work on it online.

If that won't work, tell me exactly why. Tell me the features it doesn't have but needs to. Give a better description of your current situation and what you want to end up with


Your right in my mind the only thing possible stopping me from my destination was possibly SQL.

I have a small db (14,000 records) I built it myself. I travel constantly. I want to be able to do data entry and code change on my db while at a motel. My wife needs to do data enter at here rehab hospital, (same db). I do a little in Excel but not very proficient. There is no way I could teach her Excel in 100 years. She is up in age and in a rehab hospital (permanently) for a reason. For exmple In Access if she wants to put a new entry I have to put her a cmdNew (WITH BIG NEW) She is not computer literate enough to open up for example (one drive) fined a file and open it with the appropriate app. and then save it back to the cloud. Anything I want her to do I have to put a Icon for her to go directly there.

I have just been given the password to my main office master db. About 5 of us have such access, from four locations. Look like some thing that I wanted for my own db. More I looked into it SQL kept showing up

 

[Dick7Access]

WOW! I just realized you could have gotten me into big trouble. At least one a quarter when I am at home she has me change the color of her background and buttons. Can't do that in excel. At least isn't easier than moving furniture when she was home. I had to my foot down when we were traveling in the RV. I told her I know the steering wheel will look better in that corner, but I am not moving it.

 

[plog]

Looks like you are stuck between a rock that's reluctant to learn SQL and a hard place that you think is adamant against learning anything new. Which will give?

If its the rock, then I have bad news and bad news. First the bad news, if you are going to build a custom web interface for your wife, you will probably have to write SQL. Now, the bad news; on top of and to interact with that SQL, you will have to write some web code (PHP, ASP, HTML, Javascript, etc.).

 

[Dick7Access]

Looks like you are stuck between a rock that's reluctant to learn SQL and a hard place that you think is adamant against learning anything new. Which will give?

If its the rock, then I have bad news and bad news. First the bad news, if you are going to build a custom web interface for your wife, you will probably have to write SQL. Now, the bad news; on top of and to interact with that SQL, you will have to write some web code (PHP, ASP, HTML, Javascript, etc.).

I would like yo learn SQL but don't have the time, and not sure I have the ability, but, at least you got me going down the right path. Thanks. It looks like I need to find a company that does all of that.

 

[Rx_]

First of all, you only have about 15,000 records.
How many tables hold those records. Can you classify those tables.
e.g. 5 tables for List boxes, 1 Customer table (about 10,000) 1 Notice Sent table (about 5,000 records), 4 tables for email and address phone numbers....

Back in 2001, I had many field auditors with portable PC.
The DB was actually kind of small, it was used to feed a much bigger DB.

What I did was create records with two extra fields - IsDirty (data changed) and Time stamp.

Basically - only the changed (updated) records (including newly added records) were spit into a text file, then emailed to headquarters and vice versa.
The receiver put the text box in a folder, ran a custom MERGE function.
Matching records could be updated, new records could be appended.

Once in a while, both sides updated the same record at one time. The custom Merge function identified those and asked a human to look at both of them, and pick one of them for the official record. This is called a Collision.

Granted, it takes a little coordination. The both of you can't go adding data daily and then only re-match them every month.
Since your wife's role is updating data, transferring the tables that run the combo box lists isn't necessary to update. It is usually just the core data files, and only the ones that have changed.

Lets say you had one Data table. You Update one record.
The last field on the end now reads "dirty" (your partner has un matching data) and a time stamp. At the same time, a vba routine puts a clone of the record in a "To Match" table with the "dirty" and time stamp. (it includes the Primary key for that record too)

Now, convert the "To Match" table into a comma delimited text file and mail to the partner.
Partner uses a vba import to import "To Match" record into Access "needs Updated" table.
Another routine matches the To Match record and updates the local Data Table - it also marked the last column as "updated" and time stamp.
The To Match record now gets transfered to the Dirty File - yes it goes back to sender.
It is emailed back - the sender does the same process. But this time, it finds the original record and marks the dirty column as Updated and the new time stamp.

Basically if a record is dirty - it is sent to the partner to update the partner. The partner sends it back (return receipt) so the dirty original knows the partner is back in sync with that one record.

If you have ever created a Transaction record during an edit, it is a fairly easy thing to do. Now think of sending the Transaction record to the other side to be used as an update. Then, when a record is updated, it creates a Transaction record to send back saying "we got it, we are the same now".

Many of us would love to help you figure out SQL Server, web and all that noise.
For a small time data entry, this might be an all -Access better solution.

 

[Rx_]

Sorry I have been so busy at work to actually try this. I think it should work.
It might cost about $10 a Month to have SQL Server and the hosting on the internet for up to 1 GB of data.

Basically, if they are correct, your Desktop Access (on different PC) would set up Linked Tables - to connect to SQL Server AZURE (SQL Server Cloud) just like it connects from my PC to my networked SQL Server.

What I don't have is time. However, perhaps if some of the people on this site would like to help, I would be willing to pay for 6 months of Azure.
The idea would be that a group of us would set up a Demo Access 2010 front-end (or Access 2013) that linked to something like a NorthWind or something of our choosing.
I don't have the ability to do this at my work site. At home, I am updating my technology around March 2016.

If a group of us could develop a Demo DB - with instructions on how to Link Table to AZURE, That would be wonderful. The front end should use VBA, not macros. This kind of process would benefit many of us.


https://www.fmsinc.com/microsoftaccess/cloud/link-to-azure-sql-database.html

From a Microsoft Access database, you can connect to the database and use those tables the same way you could link to SQL Server databases on your network or SQL Express on your desktop. For a fraction of the cost of buying and setting up a SQL Server box on your network, you can have Microsoft do it for you without worrying about licenses, downtime, hardware, etc., and it's available over the Internet to anyone you give the credentials for logging into it. It's pretty simple:

Open an Azure account and create a SQL Azure database
Install Microsoft SQL Server Management Studio (SSMS) for Microsoft SQL Server 2008 R2 on your machine (or Microsoft SQL Server 2012)
Use the ODBC administration tool to create a file containing the connection to the SQL Azure database
From a database opened in Access (2003, 2007, 2010 or 2013), use the ODBC connection to link to the SQL Azure tables and views

Feel free to PM me.

 

[Dick7Access]

First of all, you only have about 15,000 records.
How many tables hold those records. Can you classify those tables.
e.g. 5 tables for List boxes, 1 Customer table (about 10,000) 1 Notice Sent table (about 5,000 records), 4 tables for email and address phone numbers....

Back in 2001, I had many field auditors with portable PC.
The DB was actually kind of small, it was used to feed a much bigger DB.

What I did was create records with two extra fields - IsDirty (data changed) and Time stamp.

Basically - only the changed (updated) records (including newly added records) were spit into a text file, then emailed to headquarters and vice versa.
The receiver put the text box in a folder, ran a custom MERGE function.
Matching records could be updated, new records could be appended.

Once in a while, both sides updated the same record at one time. The custom Merge function identified those and asked a human to look at both of them, and pick one of them for the official record. This is called a Collision.

Granted, it takes a little coordination. The both of you can't go adding data daily and then only re-match them every month.
Since your wife's role is updating data, transferring the tables that run the combo box lists isn't necessary to update. It is usually just the core data files, and only the ones that have changed.

Lets say you had one Data table. You Update one record.
The last field on the end now reads "dirty" (your partner has un matching data) and a time stamp. At the same time, a vba routine puts a clone of the record in a "To Match" table with the "dirty" and time stamp. (it includes the Primary key for that record too)

Now, convert the "To Match" table into a comma delimited text file and mail to the partner.
Partner uses a vba import to import "To Match" record into Access "needs Updated" table.
Another routine matches the To Match record and updates the local Data Table - it also marked the last column as "updated" and time stamp.
The To Match record now gets transfered to the Dirty File - yes it goes back to sender.
It is emailed back - the sender does the same process. But this time, it finds the original record and marks the dirty column as Updated and the new time stamp.

Basically if a record is dirty - it is sent to the partner to update the partner. The partner sends it back (return receipt) so the dirty original knows the partner is back in sync with that one record.

If you have ever created a Transaction record during an edit, it is a fairly easy thing to do. Now think of sending the Transaction record to the other side to be used as an update. Then, when a record is updated, it creates a Transaction record to send back saying "we got it, we are the same now".

Many of us would love to help you figure out SQL Server, web and all that noise.
For a small time data entry, this might be an all -Access better solution.

Thanks, it will keep me busy digesting all that info for a while, but I love it.