Using Access to put data into SQL Server

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Using Access to put data into SQL Server

50 posts, Page 1 of 5

1 2 3 4 5 »»»

Using Access to put data into SQL Server

Rate Topic

Display Mode

Topic Options

Author

Message

mrwillcostello

Posted Sunday, September 02, 2012 3:45 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 02, 2012 3:50 PM
Points: 47, Visits: 80

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.

Post #1353283

wayne_hudson3

Posted Monday, September 03, 2012 3:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 04, 2013 5:11 AM
Points: 31, Visits: 109

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.

Post #1353402

mrwillcostello

Posted Monday, September 03, 2012 10:13 AM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 02, 2012 3:50 PM
Points: 47, Visits: 80

I am more interested in a GUI to enter data into the database. Rather than retrieving data from the database.

Post #1353591

J Livingston SQL

Posted Monday, September 03, 2012 10:55 AM

UDP Broadcaster

Group: General Forum Members
Last Login: Today @ 7:32 AM
Points: 1,442, Visits: 14,196

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 !
__________________________________________________________________

Post #1353605

wayne_hudson3

Posted Monday, September 03, 2012 4:15 PM

SSC Rookie

Group: General Forum Members
Last Login: Saturday, May 04, 2013 5:11 AM
Points: 31, Visits: 109

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.

Post #1353643

Evil Kraig F

Posted Tuesday, September 04, 2012 3:41 PM

SSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 6:03 PM
Points: 5,658, Visits: 6,099

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.

- Craig Farrell

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 | Forum Netiquette
For index/tuning help, follow these directions. |Tally Tables
Twitter: @AnyWayDBA

Post #1354227

Alan.B

Posted Wednesday, September 05, 2012 10:29 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:06 AM
Points: 223, Visits: 1,137

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.

-- AJB
xmlsqlninja.com

Post #1354735

Alan.B

Posted Wednesday, September 05, 2012 10:46 AM

SSC Veteran

Group: General Forum Members
Last Login: Tuesday, May 14, 2013 9:06 AM
Points: 223, Visits: 1,137

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.

-- AJB
xmlsqlninja.com

Post #1354751

Welsh Corgi

Posted Wednesday, September 05, 2012 12:01 PM

Hall of Fame

Group: General Forum Members
Last Login: 2 days ago @ 10:46 AM
Points: 3,820, Visits: 4,044

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/

Post #1354798

mrwillcostello

Posted Wednesday, September 05, 2012 3:16 PM

SSC Rookie

Group: General Forum Members
Last Login: Tuesday, October 02, 2012 3:50 PM
Points: 47, Visits: 80

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.

Post #1354919

« Prev Topic | Next Topic »

50 posts, Page 1 of 5

1 2 3 4 5 »»»

Permissions