September 2, 2012 at 3:45 pm
Hello everyone. So I have a database in SQL Server 2008 that contains 5 tables. I have connected Microsoft Access to this SQL Server database and have all the tables in Access as well. I can open each table individually and add data to that table which will then be stored in SQL Server. I have created a form in Access that for some reason only pulls the first record in the database (the form contains all columns from the 5 tables).
Here is the issue. I want to use this Access form (which contains all columns from 5 tables) to enter data into the SQL Server database. Please a little guidence would be greatly appreciated.
September 3, 2012 at 3:13 am
Access will only display the first record from a table on a form.
You will have to add a grid or some other mechanism to show all records.
September 3, 2012 at 10:13 am
I am more interested in a GUI to enter data into the database. Rather than retrieving data from the database.
September 3, 2012 at 10:55 am
Hello everyone. So I have a database in SQL Server 2008 that contains 5 tables. I have connected Microsoft Access to this SQL Server database and have all the tables in Access as well. I can open each table individually and add data to that table which will then be stored in SQL Server. I have created a form in Access that for some reason only pulls the first record in the database (the form contains all columns from the 5 tables).
Here is the issue. I want to use this Access form (which contains all columns from 5 tables) to enter data into the SQL Server database. Please a little guidence would be greatly appreciated.
mrwillcostello (9/3/2012)
I am more interested in a GUI to enter data into the database. Rather than retrieving data from the database.
a bit sparse on details.....
how are you connecting to SQL...odbc linked tables?
"have all the tables in Access as well"....please explain....are these replicate local tables
I assume your current form is based on a view/query/sproc of the five joined tables....now you want to enter data into this form....is this correct?
are you wishing to just create data in one of the tables or update multiple tables at same time?
as i said....more detail would help.
good luck
________________________________________________________________
you can lead a user to data....but you cannot make them think
and remember....every day is a school day
September 3, 2012 at 4:15 pm
Can you create (or get a DBA to create) a view (in Access terms a 'query') which links the five tables together in a update mode. If the links are not correct the view will be read only.
After doing this you should be able to use the wizards in Access to create a data access tabular form.
At the bottom of the form you will see navigation buttons which you can use to go to the bottom of the table and enter data there.
September 4, 2012 at 3:41 pm
Short version: You're going to have to subform the ever loving daylights out of your form. The reason is hierarchal controls in Access need to be subformed or programmed, there's really no 'middle ground'. Btw, the updatable view that was discussed above wouldn't work back in Access 2k, but I'm VERY out of date, so, I dunno.
Each recordset/table needs their own subform. You'll need to link the filtering from the higher-level form down to it to keep the subrecords straight. Getting fancy with listboxes and the like can give you relatively easy manuevering between records, but start with just the VCR buttons and make sure it's straight first.
Unfortunately, the only other (viable) alternative is to use memory recordsets which you directly program that will then drop back to the database, creating transactional disconnects and forcing you to programatically decide which record in each table to adjust depending on what data was edited. I recommend avoiding this at all costs, or getting involved in a stronger front end engine which will allow you to go full bore and make a full blown app or web-site for the data.
There's probably been a few workarounds that have come up since I got out of the Access programming scene a while ago, but I'm afraid I don't know them. These are the two basic methodologies for dealing with multiple tables in a single form, however.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
September 5, 2012 at 10:29 am
mrwillcostello (9/2/2012)
...I want to use this Access form (which contains all columns from 5 tables) to enter data into the SQL Server database. Please a little guidence would be greatly appreciated.
The best way to do this in my experience is to use a SQL Stored procedure and pass the form values to the stored proc as parameters. This way the more powerful tool, the SQL Engine, is doing all work.
This thread is pretty good:
http://www.sqlservercentral.com/Forums/Topic657473-131-2.aspx#bm1252028
This is easy to do. I don't have access to MS Access at the moment but, if you need a little more detail please let me, including the version of MS Access, and I will put together a couple screenshots and/or sample code.
-- Itzik Ben-Gan 2001
September 5, 2012 at 10:46 am
wayne_hudson3 (9/3/2012)
Access will only display the first record from a table on a form.You will have to add a grid or some other mechanism to show all records.
Just to clarify: The generic, default Access form shows only one record at a time and it might not be the "first" record ("first" record is ambiguous). There are many types of forms in access with varying behavior with respect to the number of records to show.
-- Itzik Ben-Gan 2001
September 5, 2012 at 12:01 pm
I would not try and add to 5 different tables from one form.
The easiest way to do this would be to do the following:
You could have a command button that navigates to an Access form for each Table.
You can use Subforms but that should primarily used to view the data and it is very costly with respect to performance.
If you chose not to use subforms and you do not want to see just one record that you want to display the data in a Tabular format so that it displays multiple records at a time so you can enter modify and delete from that form. You can use a wizard to create the form and then customize it.
If you have a lot of Data on the tabular or you can't view it easily from a subform then you are better off by writing an event procedures so that when you click on the record in the tabular for you navigage to a form that displays a singe record. Then you click on an Add, Update or Delete Command Button. It does not require much code to do this in Access.
If you call SP's etc and use ADO you not be able to use the built in functionality of Microsoft Access and you will have to write of ADO Code.
Best of luck.
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 5, 2012 at 3:16 pm
Ok Everyone thank you for your help. Maybe there is an easier way to do what I want to do. First let me explain what this is all about.
I am trying to build a application that will store customer and vehicle information in a database. This is hypothetically going to be used in an auto repair shop. The goal is to be able to type in a "work order" which will include customer info, vehicle info, job info, and parts info. My database currently has a Customers, Vehicles, Parts, Technicians, and Work Orders Tables.
What I need is a way to type in all the information contained in the five tables at one screen or "form" and then have it update all the tables with that info.
The database is built in SQL Server 2008.
September 5, 2012 at 4:05 pm
mrwillcostello (9/5/2012)
Ok Everyone thank you for your help. Maybe there is an easier way to do what I want to do. First let me explain what this is all about.I am trying to build a application that will store customer and vehicle information in a database. This is hypothetically going to be used in an auto repair shop. The goal is to be able to type in a "work order" which will include customer info, vehicle info, job info, and parts info. My database currently has a Customers, Vehicles, Parts, Technicians, and Work Orders Tables.
What I need is a way to type in all the information contained in the five tables at one screen or "form" and then have it update all the tables with that info.
The database is built in SQL Server 2008.
It sounds like you need to create a WOrk Order record and Insert the values that are referencing those other tables on the form.
So you create a Form that you will use to enter this Data with a search command button that you use to look up the customer's vehicles and populate the form.
You will also need to search for the Parts and click on a command button to populate that as well.
The Technician would not contain that many records so a Drop Down Control would take care of that.
Once you have al of the controls populated that are needed for the work order then click on a command button to insert the Work Order record.
You need to check out the internet or get a good book on Access Development. I had an Access 97 Development book and it was great but beware many are not very good and this book if for developers.
To perform the searches if you do not know VBA you can use Access Functions like DLookup.
If you use the internet search for Populate controls on a form or AutoPopulate, etc.
HTH.
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 5, 2012 at 4:06 pm
BTW, the form that you use to enter the data should have the recordset properties set to the WOrk Order Table. Good Luck.:-)
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 13, 2012 at 8:49 am
A customer can have more than one vehicle
A vehicle can have more than one customer (husband and wife, change of owner..)
(you may need another table here to cross reference owners to vehicles)
A works order can only belong to one vehicle
A technicial can work on more than one works order
A works order can be worked on my more than one technician
If you don't care about duplicating customer and vehicle details then you could store all the information on a single table and this makes the Access form much easier, otherwise think again about the user interface.
New works Order:
Option find existing customer/vehicle or create new
Create customer vehicle record or pull back relevant record to form in view only mode
Enter details for works order
Save works order with customerID/VehicleID
If this is a 'learn me access and SQL' project then it wil be a good one as it replicates real world events. Having worked in this industry, try the following scenarios:
what happens if a vechicle has a cherished number (vanity plate) - i.e. don't use the registration as the primary key. The vehice may get a new number and the number may get a new vehicle.
DON'T assume that a registration number is unique - we put a UK designed system into Dubai and then discovered that all 17 of the Royal cars have the same registration 🙂
September 13, 2012 at 9:47 am
It has been a while since your last post.
So how is your project going?
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 13, 2012 at 11:32 am
Welsh Corgi (9/13/2012)
It has been a while since your last post.So how is your project going?
It's probably overwhelming. I got introduced to SQL Server with an Access project a couple of years ago, and it took some time just to figure out what should be done in Access and what should be done in SQL Server, let alone to become somewhat proficient in both of them.
Greg
_________________________________________________________________________________________________
The glass is at one half capacity: nothing more, nothing less.
Viewing 15 posts - 1 through 15 (of 49 total)
You must be logged in to reply to this topic. Login to reply