December 28, 2014 at 11:34 pm
The video on YouTube is handy too... just so you can watch someone do it.
December 29, 2014 at 8:47 am
Late to the party here.
A lot of very good advice posted already. The only thing I would add is since you know you're going to eventually scale up to SQL Server, you'd be far better off in the long run to create the back end (tables, views, etc.) in SQL Server from the start and use Access only for the user interface rather than creating the whole thing in Access an scaling up from there. Not an easy task by any rate, but it will help avoid many pitfalls you'll likely encounter making the whole thing in Access first, particularly in light of such short deadlines.
Take it from someone who's used Access and SQL Server extensively in concert. SQL Server doesn't understand VBA nor the Access object model. There are also subtle differences between data types and how Access and SQL Server handle them that will leave you scratching your head once you convert.
I've been working with SQL Server for several years with probably 15 or 20 years of experience in various aspects of IT (including Access development) on top of that and honestly I feel like I'm only now "getting the gist" of SQL Server. This is not to discourage you but just to let you know SQL Server is BIG.
Learning SQL Server is like eating an elephant. You have to take one bite at a time.
But good luck and let us know how it is going.
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
December 29, 2014 at 8:54 am
I just thought of something else.
You might want to be doing some of the initial development/testing in SQL Server 2012 instead of in Access. It'll make it easier to learn and keep problems that arise from moving it from one platform to another to a minimum.
What version of SQL Server 2012 are you intending to use? There's (primarily) Express, Standard, and Enterprise.
Express is free, but features are limited.
There's another version called Developer Edition that has all the features of Enterprise, but only costs $50. You can't use it for anything but development, but having a copy will make it a lot easier for you to learn it all.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 29, 2014 at 11:18 am
Thanks so much for all the help! I've added links to my favorites, and am going to pick up some of those "accidental DBA" books (my cube is turning into a library!).
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.
I talked to my boss about the feedback, and she said if we have to start without SQL Server, that's fine. I know we will need to move up to it before long though, because we're going to chew through Access' 2GB limit. I still need to figure this out though, and make our DB as SQL-friendly as possible, to make the transition smooth.
@ SSC-Enthusiastic: When you say that SQL Server doesn't understand VBA or the Access Object model, do you mean that the code I'm running in my forms is going to have issues when I migrate to SQL Server? I've had to refer to fields in tables in the course of coding, but I tend to do that by mixing some SQL into my VBA; is that not going to read correctly after the switch, though? As long as the code stays with the front end, shouldn't it work?
@ SSC Eights!: Unfortunately, I've already got the tables pretty much done in Access. As for the version of SQL Server, I'm pretty sure it's Enterprise. I'm on vacation this week, so I'm not able to check what I've got for sure, but knowing the company, I'm 99.99% sure it's Enterprise.
December 29, 2014 at 11:59 am
IncidentalProgrammer (12/29/2014)
Thanks so much for all the help! I've added links to my favorites, and am going to pick up some of those "accidental DBA" books (my cube is turning into a library!).I talked to my boss about the feedback, and she said if we have to start without SQL Server, that's fine. I know we will need to move up to it before long though, because we're going to chew through Access' 2GB limit. I still need to figure this out though, and make our DB as SQL-friendly as possible, to make the transition smooth.
I've dealt with Access databases that reach that 2GB limit. You're going to want to switch long before you reach that limit since Access starts to slow down when it gets large.
@ SSC Eights!: Unfortunately, I've already got the tables pretty much done in Access. As for the version of SQL Server, I'm pretty sure it's Enterprise. I'm on vacation this week, so I'm not able to check what I've got for sure, but knowing the company, I'm 99.99% sure it's Enterprise.
That isn't a problem! The tables shouldn't be much different. If you'd like to discuss proper data typing to make sure you have good field types, I'd be happy to help.
Are you developing in production? I tend to buy every version of SQL Server in the Developer version because it is nice to be able to use it to learn at home. Make sure they have a development environment for you at work, even if it is just a powerful desktop/laptop. You don't want to crash production because you were trying to make it better.
Also, the "SSC Eights" is just a reflection of how many posts I've made. My name is above that. Not that it really matters, but it'll make discussion a little easier.
If you want to reply to a specific person, you can also click "Quote" on their reply to you and it'll appear in your next post. It can make conversations easier.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 29, 2014 at 1:43 pm
Stefan Krzywicki (12/29/2014)
I've dealt with Access databases that reach that 2GB limit. You're going to want to switch long before you reach that limit since Access starts to slow down when it gets large.That isn't a problem! The tables shouldn't be much different. If you'd like to discuss proper data typing to make sure you have good field types, I'd be happy to help.
Are you developing in production? I tend to buy every version of SQL Server in the Developer version because it is nice to be able to use it to learn at home. Make sure they have a development environment for you at work, even if it is just a powerful desktop/laptop. You don't want to crash production because you were trying to make it better.
The plan is to switch before hitting the limit. It's just knowing about the limit that got me looking into SQL Server to fix the problem. I'm all about being proactive!
I may have to take you up on that, when I get back to work next week. Do the data types differ from Access, at all? I feel like I have a pretty good grasp on them in Access.
I am developing in production. At the moment, I'm using the same desktop as everyone else, but I'm getting a third monitor...I'm thinking you mean I need more power though, right? I don't have the specs handy at home, but I know it's nothing high-powered. Will SQL really take that much juice to run? I haven't had trouble with Access yet, except it makes Citrix kick me out constantly, but I'll be doing SQL Server outside of Citrix, so I won't have to worry about that.
And I WOULD do something goofy on a Monday with no coffee. My bad about the names!
December 29, 2014 at 2:51 pm
IncidentalProgrammer (12/29/2014)
Stefan Krzywicki (12/29/2014)
I've dealt with Access databases that reach that 2GB limit. You're going to want to switch long before you reach that limit since Access starts to slow down when it gets large.That isn't a problem! The tables shouldn't be much different. If you'd like to discuss proper data typing to make sure you have good field types, I'd be happy to help.
Are you developing in production? I tend to buy every version of SQL Server in the Developer version because it is nice to be able to use it to learn at home. Make sure they have a development environment for you at work, even if it is just a powerful desktop/laptop. You don't want to crash production because you were trying to make it better.
The plan is to switch before hitting the limit. It's just knowing about the limit that got me looking into SQL Server to fix the problem. I'm all about being proactive!
I may have to take you up on that, when I get back to work next week. Do the data types differ from Access, at all? I feel like I have a pretty good grasp on them in Access.
I am developing in production. At the moment, I'm using the same desktop as everyone else, but I'm getting a third monitor...I'm thinking you mean I need more power though, right? I don't have the specs handy at home, but I know it's nothing high-powered. Will SQL really take that much juice to run? I haven't had trouble with Access yet, except it makes Citrix kick me out constantly, but I'll be doing SQL Server outside of Citrix, so I won't have to worry about that.
And I WOULD do something goofy on a Monday with no coffee. My bad about the names!
It isn't that SQL takes so much juice to run, you just want to be able to use a dataset similar to what production will be for testing. Otherwise you might think something is fast when it isn't. A more powerful machine will also help guard against something seeming slow when it won't be.
If you are developing in Production and there are other databases on that server, you can cause problems for those databases with a development problem in yours. That's the main reason I suggest getting a dev environment.
Access data types are similar, but not exactly the same, if I recall correctly. The main thing you want to check is if you're using the smallest data type you possibly can. You should be. Know your data and size appropriately. When you're back at work, feel free to ping me and we can walk through it together.
--------------------------------------
When you encounter a problem, if the solution isn't readily evident go back to the start and check your assumptions.
--------------------------------------
It’s unpleasantly like being drunk.
What’s so unpleasant about being drunk?
You ask a glass of water. -- Douglas Adams
December 29, 2014 at 3:24 pm
Stefan Krzywicki (12/29/2014)
IncidentalProgrammer (12/29/2014)
Stefan Krzywicki (12/29/2014)
I've dealt with Access databases that reach that 2GB limit. You're going to want to switch long before you reach that limit since Access starts to slow down when it gets large.That isn't a problem! The tables shouldn't be much different. If you'd like to discuss proper data typing to make sure you have good field types, I'd be happy to help.
Are you developing in production? I tend to buy every version of SQL Server in the Developer version because it is nice to be able to use it to learn at home. Make sure they have a development environment for you at work, even if it is just a powerful desktop/laptop. You don't want to crash production because you were trying to make it better.
The plan is to switch before hitting the limit. It's just knowing about the limit that got me looking into SQL Server to fix the problem. I'm all about being proactive!
I may have to take you up on that, when I get back to work next week. Do the data types differ from Access, at all? I feel like I have a pretty good grasp on them in Access.
I am developing in production. At the moment, I'm using the same desktop as everyone else, but I'm getting a third monitor...I'm thinking you mean I need more power though, right? I don't have the specs handy at home, but I know it's nothing high-powered. Will SQL really take that much juice to run? I haven't had trouble with Access yet, except it makes Citrix kick me out constantly, but I'll be doing SQL Server outside of Citrix, so I won't have to worry about that.
And I WOULD do something goofy on a Monday with no coffee. My bad about the names!
It isn't that SQL takes so much juice to run, you just want to be able to use a dataset similar to what production will be for testing. Otherwise you might think something is fast when it isn't. A more powerful machine will also help guard against something seeming slow when it won't be.
If you are developing in Production and there are other databases on that server, you can cause problems for those databases with a development problem in yours. That's the main reason I suggest getting a dev environment.
Access data types are similar, but not exactly the same, if I recall correctly. The main thing you want to check is if you're using the smallest data type you possibly can. You should be. Know your data and size appropriately. When you're back at work, feel free to ping me and we can walk through it together.
I think I see what you mean. I'm REALLY hoping to get away with it on what I'm using now, though. I really don't want to ask for a new computer.
They've given me my own exclusive space to play with. I checked and rechecked to make sure of that, because I was worried I might bull-in-a-china-shop it, and break things. I've been assured that there's no way I can touch anything but my own DB, in there.
Space-saving is one of the things I've put a lot of time into researching. I can't name all the data types off the top of my head, but that's why I take lots of notes. I've been shrinking things down as small as I possibly can.
December 29, 2014 at 3:37 pm
Here are a few links that might help you to get started with understanding the different data types:
and
(Old, but mostly still valid, still recommended to check for deprecated data types e.g. text/ntext)
Side note: in the "Queries" section in the link above there's a heavy reference to stored procedures. Even though it is not required (you can still select with a WHERE clause without a sproc but preferrably using a view) I recommend to have any data manipulation code (insert, update, and delete) in sproc's at the SQL Server level and call that sproc from ACCESS. That brings back the point I made a few days back regarding the decision where the business logic will be located, at the SQL Server or at the "frontend" (ACCESS)...
Biggest issues in terms of data types from my point of view will be when you're currently using on the following data types in ACCESS:
Yes/No (converted to BIT and displayed as either -1 or 0),
Simple/Double (converted to real/float where an exact numeric data type would be expected),
Hyperlink (converted to text/NVARCHAR() but might not be used as a direct hyperlink)
December 30, 2014 at 1:35 am
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.
I'd be very doubtful about that. They probably take file backups, but file backups are not SQL backups and backups of the database files aren't always usable, especially if they're taken when the system is in use. Your database, you need to make sure about the backups, because you'll be the one people are yelling at if what corp restores won't attach and isn't usable.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2014 at 1:39 am
IncidentalProgrammer (12/29/2014)
They've given me my own exclusive space to play with. I checked and rechecked to make sure of that, because I was worried I might bull-in-a-china-shop it, and break things. I've been assured that there's no way I can touch anything but my own DB, in there.
But once your system starts getting used by others, you'll need a development or test environment otherwise a change could take the system down.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
December 30, 2014 at 3:55 am
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.
I don't trust that at all. SQL Server really does do things a little differently. The ACID properties of the transactions means that standard backup routines may not be able to restore things properly. I'd suggest a test of this when you can, assuming that data loss would be an issue for you.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
December 30, 2014 at 5:24 am
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.
You can do a real quick check to see if and what kind of SQL Server backups they are (and you need to [font="Arial Black"]NOT [/font]click on OK EVER when doing this). If you right click on a database in the Object Explorer window, click {Tasks}, and then {Restore}, you should see a list of files (one MDF, possibly a DIF or two, and a fair number of LDF files) that can be restored and the dates should be quite recent. If you don't, then you're going to have to educate some folks.
Make sure you click {Cancel} to get out of that window.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 30, 2014 at 6:00 am
IncidentalProgrammer (12/29/2014)
Fortunately, I won't have to worry about doing the backups in SQL, myself. Corporate backs everything up automatically on a daily basis, and I'm told that to restore, all I'll need to do is submit a ticket, and restorations usually take place in about 30 minutes. So that's one thing that's not on my plate.
I'm so glad Gail, Grant and Jeff hit on this. I was reading the whole thread and got that sinking feeling when I read this comment. I wouldn't rely on a backup process by anyone without testing. If you don't test the backups by restoring, then you won't know they're any good. You need to do a test of their system by submitting a ticket, restoring to a separate database and then checking it. I do periodic checks to make sure my backups (both full and point-in-time) are viable. My advice on backups:
1. You need to know your backups are viable and you can restore from them. Test the backups.
2. You don't want your first restore to be when you need actually need it. Practice restoring before you need to.
December 30, 2014 at 6:17 am
IncidentalProgrammer (12/29/2014)
I talked to my boss about the feedback, and she said if we have to start without SQL Server, that's fine. I know we will need to move up to it before long though, because we're going to chew through Access' 2GB limit. I still need to figure this out though, and make our DB as SQL-friendly as possible, to make the transition smooth.
Another good reason to build in SQL Server from the start in your case.
IncidentalProgrammer (12/29/2014)
@ SSC-Enthusiastic: When you say that SQL Server doesn't understand VBA or the Access Object model, do you mean that the code I'm running in my forms is going to have issues when I migrate to SQL Server? I've had to refer to fields in tables in the course of coding, but I tend to do that by mixing some SQL into my VBA; is that not going to read correctly after the switch, though? As long as the code stays with the front end, shouldn't it work?
Let me introduce you to one of the "key phrases" of SQL Server (and likely databases in general): It depends.
Jet SQL - the flavor of SQL used by Access - is fairly similar to T-SQL used by SQL Server, but there are some notable differences. If your VBA/SQL code is referring to objects strictly in the Access front-end, you will probably be okay. Things like pass-through queries (queries that exist in Access and get passed to SQL Server for processing) can burn you. Pass-through queries become important when you want to execute SQL Server stored procedures from Access (and you will, trust me).
It's perfecctly acceptable to create something in Access and scale it up to SQL Server. If that's your comfort zone, by all means go for it. But since you're intending to scale up anyway in a relatively short period of time, I think you'd be better served focusing your efforts on learning SQL Server and how to create your solution there then figuring out how to connect up with Access.
Again, good luck.
P.S. What Gail, Grant, et al., said about backups. The question is how much data can you afford to lose?
____________
Just my $0.02 from over here in the cheap seats of the peanut gallery - please adjust for inflation and/or your local currency.
Viewing 15 posts - 16 through 30 (of 57 total)
You must be logged in to reply to this topic. Login to reply