Wrox Programmer Forums
| Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old October 19th, 2007, 02:43 PM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default Access DB to SQL

Sorry gang...I know this is last minute, but...
I was handed this really HOT project of a database and it has an unrealistic deadline AND they want it on our SQL server, which is uncharted waters for me. (Never upsized or developed a database on SQL server before.)
Can anyone think of any useful links for some weekend reading?

I have found some items on the upsizing process, but I still can't find anything on how to modify the table after it gets on SQL.

I need to keep the front-end as an .mdb or .mde.

Thanks!

Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
__________________
Regards,
Laura

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
 
Old October 20th, 2007, 05:52 AM
Registered User
 
Join Date: Oct 2006
Location: Leigh, Lancashire, United Kingdom.
Posts: 41
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Hi Laura,

This is a link I use quite a lot. All various types of databases, with useful links on it - - http://databases.about.com/
 
Old October 22nd, 2007, 07:07 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

How is this working out? I do a LOT of Access/SQL. Still need any help?

You will want SQL Server Enterprise Manager and Query Analyzer on your desktop at a minimum.

Lemme know.


mmcdonal
 
Old October 22nd, 2007, 08:55 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

Well...I haven't tried upsizing to SQL yet. I'm waiting on our IT department to get back with me. It took virtually all weekend, but I think I have the database ready. I do not have either app/tool you mentioned installed on my PC, so I'll have to see if they can install those for me.
(I'm starting to doubt myself on if I'll be able to find the location of the server & connect to get the database up on SQL because our IT department is AS400 based and we don't have anyone who knows SQL or Access very well...they call me the "expert" and I'm not even part of the IT department...and never worked with SQL server before. This will be a trip!)

The main reason I was asked to put this on SQL is because of network speed at other locations. This database will grow really quick and they do not want the normal "slow" speed of the global network drive at our other locations. The server is in Indiana, but I have read that when using SQL server with Access it decreases time spent transferring data back and forth, and hence increases speed. (Our other locations are in Arkansas, Mexico, Massachusetts, China, Czech Republic, & Europe.) So I figured that the data could be stored on the SQL server, and then the front end could be on a local network drive at each location to keep the speed as quick as possible; while keeping everyone using the same data source. Does it sound like I understand the speed portion correctly?

Any ways...As soon as they let me know when I can meet with our 'network guy' we'll give it a try and I'll be sure to let you know if I have any issues or questions. (You can probably count on some questions!) :)

Thanks!


Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!
 
Old October 22nd, 2007, 09:18 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You will want the enterprise tools to allow you to work on SQL Server. Enterprise manager is a good interface to allow you to work with Tables and Views (like Table and Query objects in Access.) I think once you have this interface, the anxiety level will go down a lot. The View designer is a lot like Query Designer in Access.

Query Analyzer will allow you to do ad hoc queries of the data that you can then either package as a View on the server, or run in strings from Access.

Once you have your tables and views set up, you will need to use ADO to connect to them. I would suggest using a disconnected solution (no local tables) but this will require that you build provider statements and take snapshots of the data for your users to work with - unless you think they will be working with dat for long periods of time, in which case you will want linked tables. This will require installing the proper DSN(s) on each users' desktop. Provider statements do not require DSNs and so are much more portable.

http://wrox.books24x7.com has tons of information on these solutions, and is always available, so that is what I use mostly for figuring out the little bits and pieces.

You will need to get familiar with flow control using ADODB.Recordsets. How are you with that?

mmcdonal
 
Old October 24th, 2007, 07:03 AM
Friend of Wrox
 
Join Date: Jun 2003
Location: Connersville, Indiana, USA.
Posts: 126
Thanks: 5
Thanked 0 Times in 0 Posts
Default

I don't think I have ever used ADODB.Recordsets. Surely one of my books will have something in them on it. I just purchased 2 new ones based on Access 2003, which is what we are running here. They are "Programming Office Access 2003" by Rick Dobson, and "Access 2003 Power Programming with VBA" by Allen Taylor & Virginia Anderson. I am hoping they will be as helpful as the one you (mmcdonal) recommended for security.

I was reading how the upsize wizard will take queries and store them as views, but I'm not sure how that would work with this database though. I am most worried about the reports because the user has the option to specify different criteria for the reports...hence the big Where Statement post I had last week where I kept dorking up the quotes.
Several of the forms open based on the value of a field on a currently opened form too. So I'm not sure if I would be able to store the queries as views to gain much speed.


Regards,
Laura

FYI...My user id has changed. My old id was lbreitenbach

The only thing standing between you and your goal is doubt. Quit doubting yourself and you'll be able to accomplish anything!




Similar Threads
Thread Thread Starter Forum Replies Last Post
can't access db in sql server 2005 brillox BOOK: Beginning ASP.NET 2.0 BOOK VB ISBN: 978-0-7645-8850-1; C# ISBN: 978-0-470-04258-8 3 May 3rd, 2007 05:15 PM
SQL query on Access db giving error DesignsOnline.co.uk Access 2 November 17th, 2006 05:06 PM
access db to sql server db mikersantiago Classic ASP Basics 4 November 16th, 2004 03:33 AM
Switch from Access DB to SQL sporkman43 Classic ASP Basics 7 August 8th, 2003 10:43 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.