I know you're advice is right on the money. I hate it, but it's spot-on.
OK, I've just taken a deep breath and held it for a few seconds.
I'm going to read-up over the weekend and take-in as much as I can. See how far I get by Monday from a standing start. One issue though I don't gave access to SQL server so perhaps the free version of Visual Studio?
Where would you recommend I begin?
One other thing SQL or MySQl which would be easier to build and maintain?
How would I decide between Microsoft's SQL Server or Oracle's mySQL would be based on the industry, customers and your competition. Perception does matter.
In general I would go with Microsoft's products. The reporting services in Microsoft's SQL Server is really great.
When you do a rewrite it really is best to start over from scratch. Also if you really want to learn how to use SQL server then I would not simple upsize (copy) your current back end. Now is the perfect time to rethink your design and make changes to take advantages of SQL Server. This also allows you to apply everything you have learned since you started. No garbage gets carried forward.
Sorry my mistake I thought sql server express was an integral part of Visual Studio, my mistake.
Anyway, just finished downloading and installing sql server express 2008 r2 and feeling empowered already. Looks like it's going to be a late night form me.
I'm eager to see how the tables will look once in SQL Server and also how to connect with ODBC, so I'm going to do the convert from Access first, play around for a while and then go back see how I can re-create the tables from scratch.
Yes, the whole point of the re-write is to improve and also to introduce new functionality, most of which is lurking somewhere in the back of my mind. If I can manage to get those thoughts into a working modal within reasonable time then I'll have my last stand against winning back ground from the multinationals. Should be a whiz
The Microsoft® SQL Server® 2008 R2 Express RTM is a powerful and reliable data management system that delivers a rich set of features, data protection, and performance for embedded applications, lightweight Web Sites and applications, and local data stores. Designed for easy deployment and rapid prototyping, this download includes support for Sysprep, Microsoft's System Preparation Utility for Microsoft Windows operating system deployment.
SQL Server Express is designed for easy deployment and rapid prototyping. This edition is designed to integrate seamlessly with your other server infrastructure investments. Management tools are not included in this download. For SQL Server Express with Management Tools, please downloadMicrosoft SQL Server Express with Management Tools .
Smalltime I've had the same experience with sql server as yourself. In my experience (I'm only very inexperienced) the setting up and linking really is the hardest part. Documentation is sketchy and if your like me its a series of subjective steps that people have just decided to call particular names. It seems totally standard practice for instance to not realise that you need SSMS. Coming from applications it seems to have a completely separate GUI (effectively) and there other confusing things for first timers - DSN seems to be the SQL server variant on ODBC for instance... and people giving you advice often get that term confused with DNS which is thoroughly confusing.
There is lots of reading but it all seems overwhelming.
I had a similar problem when I first tried upsizing my problem was that I didn't have the correct permissions to create tables in SQL Server. The security permissions are somewhat different from Access. The first time I tried upsizing it wouldn't let me because I didn't have the correct permissions. The second time I tried I imported it into the wrong database. The DBA helped me out but I really was shooting in the dark.
I am getting there very slowly now but once I'd made those initial mistake I upsized a database in full and it literally took seconds to do it. Plus the database seems to be working no problem. I kept all the queries in my front end so I suspect I'm hardly using any of SQL's power but hey I was just happy to be linking to a SQL Server back end and have (in my eyes) a proper two tier system that seems to work.
Get it working first and then I 'll worry about the long road to polishing it.
If you're really having problems you could pay for a days worth of time for a dba to come and point you in the right direction. If you're like me my dba only needed half an hour with me to really get me over some real basic problems which were entirely because I am so naive. Remember though steps involved in upsizing are somewhat subjective menu navigation. It 's not necessarily something can just know through logic in which case its a case of figuring it out by iterative experimentation (posh term for guessing) which lets face it is really dull.
If the application is that valuable hiring a guy to teach you how to upsize access databases to SQL Server on your own computer could be a real winner and not extortionate. Your obviously well capable of taking it from there...
Hi Tech could probably assist you over the phone in a day provided you would accept that your going to have to pay to potentially have him on the phone while you load up programs ( assuming your in different geographical areas )
The good thing about being at the bottom of the ladder is that you are probably making very simple easy to fix mistakes. Just remember that and the fact that all things are difficult before they are easy.
like you I'm working going to try and get it working first and then polish later. But as HiTechCoach suggest I'll be then be going back and remaking from scratch. If I'm going to play with another technology then I really need to be reasonably competent before throwing it at my customers.
Of course initially I know it'll be a case of fumbling around in the dark trying to make sense of everything, but once I have a modicum of understanding I'm pretty sure the rate of improvement will pick up pretty quickly. I then might even as you suggest take a day or two of tuition.
At the moment I'd just be happy to get SSMS up and running. Gave up when I started falling off my chair at about 4am yesterday. Finally un-installed everything and did a system restore (just in case). I be giving it another bash again today.
<<DNS seems to be the SQL server variant on ODBC for instance>>
Not really. They actually work together. You don't have to use a DSN. To learn more see: Using DSN-Less Connections
Basically a DSN stores the connection string information.
It’s 3.20am and finally after umpteen installs and uninstalls running every diagnostic tool in my library and uninstalling a host of other things I can’t even remember now, I got down to the culprit. Powershell 2 doesn’t like SSMS or rather SSMA doesnlt like powershell 2.
No Powershell 2 uninstall! So more hacking through Reg Keys. Then of course I realised that I’d got rid of Microsoft’s standalone windows installer so couldn’t run MSU files anymore.
Almost gave up the will to live, but in the end after I’d chopped through a forest of registry keys manage to get SQL server 2008 express R2 and SSMS up and running, so will start learning all there is to know about building SQL SERVER tables tomorrow.
Having successfully convert my tables to SQL Server Express I'm in the process of creating links. How grateful I am now for Lightwave's post and HighTechCoach's links re the subject.
Haven't quite managed it yet as I'm still trying to digest the DNS-less connection method and how I'm going to manage this in deployed\production environments.
Just thought I'd stop reading for a while and come back to give you guys a BIG BIG thanks.
To get started I would create a DSN. This will allow you to link tot eh table like you are used to. You just have to change the type to ODBC in the drop down list when you create the linked tables.
TIP: Make a copy of your back end used this tio upsize with SSMA. Tell SSSMA to create the linked tables for you. Next import the linked tables from the upsized back end into your front end after deleting the linked table to the Access back end.
I don't know how far down the line you have got to getting DSN Less linking working(Its DSN not DNS, my fault I mixed them up when I first told you(I've edited out the mistake in my original post!) The following are my instructions that I have written for myself to help me in general setup on new projects. Along with the function that you need to have in your front end (Access 2003)
Much of this will be repeating things HiTech has linked to..but thought it might be useful nonetheless
INSTRUCTIONS
First off create/go to the Access 2003 front end you wish to benefit from a DSN less connection(s) and create a module with the code that is listed within the code box below Function name: AttachDSNLessTable
Now create/go to a/the Autoexec macro and create a run code instruction with a format as below. For EVERY table for which you want a DSN Less connection have a run instruction and call the function and pass across your project specific parameters. It will have the following format.
The exact description of the parameters are defined within the function but I copy them here for clarity
stLocalTableName : the name of the local table (In this case T002)
stRemoteTableName : which is the name of the table in the SQL Server database (In this case T002)
stServer : Name of the SQL Server (and path) that you are linking to (The computer is called HV12K2J on the network and its under the SQLExpress directory)
stDatabase : Name of the SQL database on that server that you are linking to
stUsername : Name of the SQL Server user who can connect to SQL Server leave blank to use a Trusted connection (Blank above)
stPassword : SQL Server user password (Blank above)
Code:
'//Name : AttachDSNLessTable
'//Purpose : Create a linked table to SQL Server without using a DSN
'//Parameters
'// stLocalTableName:Name of the table
'// stRemoteTableName: Name of the table that you are linking to on the SQL Server database
'// stServer: Name of the SQL Server that you are linking to
'// stDatabase: Name of the SQL Server database that you are linking to
'// stUsername: Name of the SQL Server user who can connect to SQL Server, leave blank to use a Trusted Connection
'// stPassword: SQL Server user password
Function AttachDSNLessTable(stLocalTableName As String, stRemoteTableName As String, stServer As String, stDatabase As String, Optional stUsername As String, Optional stPassword As String)
On Error GoTo AttachDSNLessTable_Err
Dim td As TableDef
Dim stConnect As String
For Each td In CurrentDb.TableDefs
If td.Name = stLocalTableName Then
CurrentDb.TableDefs.Delete stLocalTableName
End If
Next
If Len(stUsername) = 0 Then
'//Use trusted authentication if stUsername is not supplied.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";Trusted_Connection=Yes"
Else
'//WARNING: This will save the username and the password with the linked table information.
stConnect = "ODBC;DRIVER=SQL Server;SERVER=" & stServer & ";DATABASE=" & stDatabase & ";UID=" & stUsername & ";PWD=" & stPassword
End If
Set td = CurrentDb.CreateTableDef(stLocalTableName, dbAttachSavePWD, stRemoteTableName, stConnect)
CurrentDb.TableDefs.Append td
AttachDSNLessTable = True
Exit Function
AttachDSNLessTable_Err:
AttachDSNLessTable = False
MsgBox "AttachDSNLessTable encountered an unexpected error: " & Err.Description
End Function
Well what can I say, seems like you're reading my mind and giving me the very exact advice in the most timely way. I was, as you'd probably guessed struggling with creating the linked tables which I've now completed after reading your guide, extremely helpful.
In fact after reading your guide HiTechCoach's link now make complete sense. I think I'd immersed myself into too many different aspects and couldn't see the wood for the trees.
I didn't go for the AutoExecute macro (using 2010) but instead made calls to the AttachDSNLessTable function from a button on an Admins form. Also I've implemented a user updateable variable for the server location to allow for runtime changes to be made.
I'm sooooo pleased with the outcome that I might even take a couple of hours off. (that's a rear thing for me nowadays).
Although I have a couple of issue, like when saving SOME records I get;
Run-time error '3146':
ODBC--Call failed
[microsoft][ODBC SQL Server Driver][SQL Server] Coversion failed when
converting date and/or time from character string. (#241)
at least I'm well on my way to tackling one of my biggest problems which I've been putting off for ages. Now that it's done it seems all so easy! In fact it makes me wonder why MS hadn't implemented this within Access's link table manager.
After conversion and linking, I've noticed that some of my forms and modules no longer work and am now busy trying to resolve these issues. E.g. it seems I have to add dbSeeChanges in various parts of code but haven't yet quite grasped the rules as to where to use it.
Anyhow, my main problem at the moment is the date format. I can't use the British format "dd mm yyyy" as it seems SQL server only accepts the american format. "yyyy mm dd". For example in one of my data entry forms I have the properties of a text box set to;
Format - Short Date (system is set to English(United Kingdom)
input mask -00/00/0000;0;_
No matter what I do SQL server wont accept this as a valid entry and access throws an error after an aborted save.
Also many, OK all, of my list boxes that show a date are now displaying the american format.
I know, without having to ask that my users wont accept inputting in another format other then the one they're accustomed to. So what can I do? What's everyone else doing? I'm sure I can't be the only one facing this dilemma.
