September 13, 2012 at 1:39 pm
I have put the project on hold for a bit. Need to work more with Access and understanding the form creation and how to create list boxes to select year, make, model.
September 13, 2012 at 1:45 pm
Look at the concept of n-tier architecture, or MVC patterns.
The idea is to separate out different processes and let each tool carry out it's area of expertise.
E.g. Let SQL do all the heavy lifting of manipulating data (inserts, select lists, record updates etc); do this by only allowing access to the tables through stored procedures (for insert, update and delete) and views (for selects)
Let Access do what its good at - Forms and reports - (have I just opend a can of worms???)
Access should ask SQL for data, or pass back data to the stored procedure. Access should not know or care HOW SQL does this. SQL should not know or care how Access decides what data to ask for or how it prepared it.
The logic behind this is that if you decided that you wanted to replace the Access user interface with a web based interface (or .Net, or IoS, Android, or whatever) the business logic and database structure are secure. If you decided that you want to move from SQL to Oracle (heaven forbid 🙂 ) then the Access front end does not need to change as long as the connections to Oracle work the same way.
There are challenges, like where to put validation (form validation such as are the fields all completed, is the start date before the end date etc would normally go in the user interface layer; rules such as no duplicate records, valid selections would go in the business rules layer [often but not always integrated with the database] but there are many validations that sort-of-cross-the-boundary between form and business logic)
Start with something simple, but of practical relevance to you and don't forget to ask for help when you need it. Personally I find it immensly satisfying to finally crack a programming problem that will make my life easier; I use SQL, Reporting services, Excel VBA, Access VBA, PHP with Codeigniter, Crystal Reports and Progress. What you learn in one often helps in the others. If you learn to think like a computer program, you will come across the same problems often, and it is simply a matter of learning the syntax.
September 13, 2012 at 2:00 pm
mrwillcostello (9/13/2012)
I have put the project on hold for a bit. Need to work more with Access and understanding the form creation and how to create list boxes to select year, make, model.
Google "access cascading combo box", and you will have a whole host of material. One thing is for sure, you will most certainly need to learn some basic VBA, or your application will be limited to what you are able to do with the Access wizards.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 13, 2012 at 2:10 pm
yeah, don't use the wizards, learn to programme - it will be quicker in the long run and the next developer will thank you for it.
September 13, 2012 at 2:12 pm
aaron.reese (9/13/2012)
yeah, don't use the wizards, learn to programme - it will be quicker in the long run and the next developer will thank you for it.
...and then you will be a code junkie like the rest of us here 🙂
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 13, 2012 at 7:42 pm
Yeah what you are trying to do is probably more that you initially thought.
I would try and keep it simple. Start by adding list boxes one at a time on a new record.
You will need a lookup table for your Year List Box as the Data Source.
The Make and Model gets a little trick. When you select the make, the entries for the model should be filtered for that model so that is probably not the easiest place to start. 🙂
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 26, 2012 at 2:53 am
Hi
I've linked Access to SQL Server via ODBC, it links to updatable views that a few selected users can use for read/write. The rest link to SQL via Access so they can write their own queries using Access' very friendly query tools.
As I understand it you want a nice structured GUI for the user to input data into SQL Server.
1. You can create an Access mdb that lives on the network. In this you can link to the SQL tables you are interested in. I would recommned creating a File DSN and store this on the network, this will save creating a DSN on every users' machine. You should be able to then code in Access as normal.
You will be using DAO data access then and this works well with Access data but not all features will not be available as you are connecting to SQL (see SQL pass thorugh)
2. You could still have an Access DB but this time with no links to SQL (no tables). Instead you use ADO data access to connect to SQL and this will work better than DAO.
I've never done a front end in Access so its theory for me, but I think all users will need Access installed.
I would use another programming lang (.NET) to create a standalone exe, no need for access on client machine.
September 26, 2012 at 1:06 pm
terry999 (9/26/2012)
HiI've linked Access to SQL Server via ODBC, it links to updatable views that a few selected users can use for read/write. The rest link to SQL via Access so they can write their own queries using Access' very friendly query tools.
As I understand it you want a nice structured GUI for the user to input data into SQL Server.
1. You can create an Access mdb that lives on the network. In this you can link to the SQL tables you are interested in. I would recommned creating a File DSN and store this on the network, this will save creating a DSN on every users' machine. You should be able to then code in Access as normal.
You will be using DAO data access then and this works well with Access data but not all features will not be available as you are connecting to SQL (see SQL pass thorugh)
2. You could still have an Access DB but this time with no links to SQL (no tables). Instead you use ADO data access to connect to SQL and this will work better than DAO.
I've never done a front end in Access so its theory for me, but I think all users will need Access installed.
I would use another programming lang (.NET) to create a standalone exe, no need for access on client machine.
I do not agree with anything that you said in your reply, what you have provided is very bad advice.
You can create an Access mdb that lives on the network. In this you can link to the SQL tables you are interested in.
In the scenario that you are describing you do not generally have just one mdb on the network that users access. You create an mdb from the accd (mdb in older version) and use that to link directly to the SQL Server Tables.
You create an accde previously known as an mde to secure the Access Database so that no one can make changes.
Then you create an other mdb for the GUI that resides on the client and you link to the network accde.
I do not agree that File DSN is not the most secure. You can push down the DSN's to the clients progamatically and update the registry on each client machine.
ADO has been in use for a long time in Access, I recall using in in Access 97.
If you decide to write code and not use the bulit in features on Access then you will use ADO but I do not see this as a requirement for the OP.
You are confusing this with what used to be called an Access Data Project (ADP). Yes there are no linked tables. Using ADO does not eliminate the need to link a table.
You create an Access Runtime and there is no need to install Microsoft Access on the Client. The tool that I used to use was the Micorosft Office Developers Toolkit.
runtime distribution:
http://www.microsoft.com/en-us/download/details.aspx?id=10910
instructions to create accde file:
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 27, 2012 at 5:03 am
Welsh Corgi (9/26/2012)
You create an accde previously known as an mde to secure the Access Database so that no one can make changes.
Then you create an other mdb for the GUI that resides on the client and you link to the network accde.
I could not agree more with this, especially if you will have several users using it over a network. I had one a few weeks ago that had been working fine over the network when I was the only one working on it. Once I released it to some users, who all used the same copy over the network, it only took about a week for all the modules to disappear. As it turned out, I had a tiny bug that would not compile, and the rest was history. Good thing I had backups and could turn it into .accde. Read this, http://social.msdn.microsoft.com/Forums/en-US/accessdev/thread/e041f080-de68-48f5-a624-68b24d9af314
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
September 27, 2012 at 8:47 am
Your point in separating the code from the data is a very good one (accde I think you called it). I also luckly had no code in Access my code wa sin an EXE.
I dodn't agree with having to write code to ship a DSN config to every user's machine. What if DB location changes, authentication changes. Its another thing to maintain. Although you could configure some users to connect with windows and others with SQL authenticaton. I'm happy with network file dsn I change it in one place.
I've taken several VB apps that used bound controls e.g. DBGrids etc etc that bound well to Access DB's, when they bound to Access DB's that linked to SQL Server via ODBC I had lots of problems. In most cases I removed all databound controls the ones that remained I changed to ADO. That was just to get things working, in time now I've removed all databound controls.
Just taking VB code that worked well talking to Access failed when talking to the same access mdb but all its tables were linked to SQL Server. It datacontrols were deadlocking aaginst each other as soon as you run 2 instances of the exe.
I tried SQLPassthrough , lockoptimistic etc to no avail. I know it wasn't DAO's fault because ages ago I was using DAO successfully with SQlPassthorugh to talk to SQL Server - no datacontrols!!
My question is surely these DAO bound controls are the same one Access uses (I get confused DBGrid is DAO dataGrid is ADO or other way around) so they may have the same problems.
Couldn't you in Access not link tables at all and just say
dim oCon as new adodb.connection
dimn oRS as new adodb.recordset
ocon.open(yourconnectionstring/dsn)
call oRS.open("Select * from myquery", ocon)
set mydataboundcontrol.datasource = oRS
I think this would work with DAO recordsets but I don't think you can dynamically bind DAO RSs
September 27, 2012 at 9:17 am
terry999 (9/27/2012)
Your point in separating the code from the data is a very good one (accde I think you called it). I also luckly had no code in Access my code wa sin an EXE.I dodn't agree with having to write code to ship a DSN config to every user's machine. What if DB location changes, authentication changes. Its another thing to maintain. Although you could configure some users to connect with windows and others with SQL authenticaton. I'm happy with network file dsn I change it in one place.
I've taken several VB apps that used bound controls e.g. DBGrids etc etc that bound well to Access DB's, when they bound to Access DB's that linked to SQL Server via ODBC I had lots of problems. In most cases I removed all databound controls the ones that remained I changed to ADO. That was just to get things working, in time now I've removed all databound controls.
Just taking VB code that worked well talking to Access failed when talking to the same access mdb but all its tables were linked to SQL Server. It datacontrols were deadlocking aaginst each other as soon as you run 2 instances of the exe.
I tried SQLPassthrough , lockoptimistic etc to no avail. I know it wasn't DAO's fault because ages ago I was using DAO successfully with SQlPassthorugh to talk to SQL Server - no datacontrols!!
My question is surely these DAO bound controls are the same one Access uses (I get confused DBGrid is DAO dataGrid is ADO or other way around) so they may have the same problems.
In a previous post you mentioned that this was theory for you and I can appreciate you comments about you past experiences.
As far as the File DSN, it is not very secure. You do not have to write code to push registry enteries to client PC's. It is usually handled by the Network Support Team in an origanization. If something changes you push an update down to the clients. It is a piece of cake and it is commonly used when Servers are moved.
System DSN's are by far the most used and File DSN's the least. That's not theory that's previous practicle experience for me.
My response was not about what you or I can do. I thought that we were supposed to be helping him?
Sure you can write ADO and use unbound controls, pass through queries, etc.
The OP is not ready for what you are noting that can be done.
Perhaps you may want to open another thread about your practicle experiences with Access?:-)
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 27, 2012 at 9:41 am
You're right.. I've muddied the waters somewhat..
I guess though he like me works in a place where the Network Support Team would ask me to do it.
September 27, 2012 at 1:28 pm
terry999 (9/27/2012)
I guess though he like me works in a place where the Network Support Team would ask me to do it.
Writing the code is not that hard. You may not have permissions to deploy it.
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
September 28, 2012 at 6:24 am
You could use a DSN-less connection with Windows authentication, which would look like this:
ODBC;DRIVER=SQL Native Client;SERVER=YourServer;DATABASE=YourDatabase;Trusted_Connection=Yes
you don't have to worry about type or location of a DSN.
September 28, 2012 at 6:49 am
Yes a DSN-less connection is an option.
It may not be the first choice that someone who is just learning Access but listed below are some links to articles on DSN-less connections to Microsoft Access.
http://www.accessmvp.com/djsteele/DSNLessLinks.html
http://support.microsoft.com/kb/892490
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
Viewing 15 posts - 16 through 30 (of 49 total)
You must be logged in to reply to this topic. Login to reply