Excel Front end, SQLServer backend???

[JapanDave]

I posting here as I can program Excel with access on the back end. I want to know if access queries strings are the same for SQLserver? I am going to need a database with more storage sapce than what access provides.

I was thinking of designing a Database in Access and then transferring it to SQLserver. I would really appreciate what anyone has to say with experience in these databases and how to go about what I have in mind.

Cheers.

[Movian]

I would suggest 2 things. First access is great for setting up user interfaces for databases. so use that for your front end. Second i would suggest using SQL from the start as this will ensure that when your done there is no additional conversion work and everything will be set to go at that point.

[JapanDave]

@Movian,

Sorry for the very slow reply. I have been researching my options and I am beginning to think that excel is not the bets way to go. Too many pages to setup and I can see the code being a night mare.

I have been looking at Visual Studio and using that as a front end. The learning curve is steep, but that may be the better way to go. Ever used VS for SQL Server. I am a bit lost as to which app I should use for a in house server used to store data on clients and orders etc.

BTW, can a mod delete the above post?

[Aussiebear]

Hi JapanDave, Feel free to comment on any posts that you feel are inappropriate. I'll happily take a look and take action where necessary.

[Bob Phillips]

Access connection strings are not the same as SQL server connection strings, but there are plenty of places too get them from.

SQL can also be a tad different, but SQL Server is the way to go (IMO).

What I have done is to build a database manager class where I pass the DB type and it makes all the connections etc. My DB Manager then handles all request, run SQL, exec SP, update, retrieve etc. The coding might be long, but it is anything but a mare.

I would caution against going VS if you haven't already been there, it is a huge learning curve. Excel can handle what you want easily.

[JapanDave]

VBA was a steep learing curve for me and I have always wanted to learn VB6 ,C# and html. So I was figuring that this was a good place as any to start? Or is VS a lot more difficult than VBA?

[Bob Phillips]

If you have good Excel experience/knowledge, VBA has a context for you that makes it a gentler introduction. VBA is a self-service language, and can readily be picked up by business bods who are using Excel, whereas the .Net languages are far more of a development tool and benefit from learning development paradigms, structures, and the paraphernalia that goes with it - hence the steeper learning curve.

Learning VB6 seems somewhat pointless to me. The language is the same as VBA, it just has a separate IDE (Visual Studio) and different ways of building, such as forms, but it is far more effectively dead than VBA is or ever will be. The .Net languages might be somewhat easier knowing VBA if developing Excel based solutions, but it is still a world away.

As for HTMl, that has no bearing on VBAor VB.Net/C#, but really for web based stuff you should be learning JavaScript as well.

What you look to learn should be driven by a specific need, which should in turn direct what is the best choice, otherwise you will get nowhere.

[JapanDave]

Thanks for the explanation. Man, I am behind the times. I am still researching which is the best way to go, but thanks for your time.

Dave