October 14, 2011 at 4:48 am
Hi Guys,
Sorry for the awful pun !
I am an experiecen DB developer have just started developing MS SQL Server 2008 and have been doing alot of reading. I have a project to develop a databse that will be based around 3 tables (totalling about 150,000 records over 10 years) with a MS Access Front End (FE)
I am researching whether I should do this be linking my sqlserver tables to my MS Access FE or let my FE access the SQL Server 2008 database via Stored Procedures.
From read in other post on the net, it appears using SP seems to be the preferred choice of 'Professionals', but I am interested in everyones view on this?
Being a newbie to SQL Server I have already created my tables relationships etc and have them at 3rd NF (Boyce Codd) but find the SP part a bit daunting.
Any links to where I can learna bout SP quickly would be a great help.
So you advise will be greatly appreciated
kinf regards to all:-D
October 14, 2011 at 7:43 am
October 14, 2011 at 7:47 am
shanesmith142 (10/14/2011)
hgfhgd
Reporting SPAM!
October 14, 2011 at 8:19 am
Back to the topic after the commercial break...
It doesn't really matter if you link your tables or not. If it were my project I would not link the tables at all and get my data via stored procedures. It can seem a bit daunting at first but don't get too scared. They aren't anything magical. They are just a way to do some data manipulation. If all you need is a way to retrieve data you can use views. If you need to do conditional processing and such you can use stored procs. If you need some help getting started post back with some details about what you are trying to do and we can have a go at getting you pointed in the right direction.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 8:44 am
Hi,
Thats great, I am just getting it all togther and practicing with SSMS etc. I have my tables and realtionships created.
So in my Access databse I have form FrmClients I want it so show the FName, SName and DOB from tblClient.(Its a small table with only 6 records in SqlServer MSSQLSERVER and a db called FirstDatabase)
I am using ms Access 2003 and SQl Server 2008 R2 so if anyone can give me the SP code to try????:-D
October 14, 2011 at 9:09 am
I see you are new around and sounds like fairly new to sql. It is pretty much impossible for anybody to give you the stored procedure code. We need a bit more info to go on. I can give you a basic skeleton you could use for something like getting your clients. If you want actual code you will need to provide ddl, sample data and desired output based on the sample data. Check out the first link in my signature for best practices and instructions on how to gather and post this type of stuff.
Here is a sample you could use as a starting point.
create procedure GetClients
as begin
select FName, LName, [other columns]
from Client
end
Probably a better choice for just a simple select like this would be a view instead of a stored proc.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
October 14, 2011 at 3:21 pm
Thanks again SSCrazy.
I have linked tables working now between access 2993 and sql server 2008 and for me that's a big step forward.
Although all my tables are linked when I open them I get #DELETED in the fields, except for one table that shows the data correctly, can anyone explain why that is happening?
Also I would love to write script to access SQL Server (not using ODBC), if I could get a simple script for opening a ADO connection from access 2003 to SQL server and update the TblCLients that would be great too?
I have tried a few off the internet but without success.
All help appreciated
October 14, 2011 at 3:39 pm
Tallboy (10/14/2011)
Thanks again SSCrazy.I have linked tables working now between access 2993 and sql server 2008 and for me that's a big step forward.
Although all my tables are linked when I open them I get #DELETED in the fields, except for one table that shows the data correctly, can anyone explain why that is happening?
Also I would love to write script to access SQL Server (not using ODBC), if I could get a simple script for opening a ADO connection from access 2003 to SQL server and update the TblCLients that would be great too?
I have tried a few off the internet but without success.
All help appreciated
The issue of #DELETED is one of the reasons I suggest accessing your data directly instead of the link tables. Link tables can be a pain in the backside to get setup correctly, at least for me since I don't use them very often.
To have Access open a connection to sql you will need to use VBA and a database connection. You can find tons of examples of connecting to sql server from VBA on the internet. Take a peek at connectionstrings.com when you need to figure out the connection string.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply