-1

I have converted a jet database to a sql server 2005 express database.

The front end app contains a lot of inline sql that was written to query the jet database and this is now not working. The jet sql contains terms like True, False. String literals are contained in both single and double quotes etc, Functions like ucase, trim, isnull etc are present in the sql.

Rather than re-writing hundreds of queries, is there a way of making sql server use jet sql?

jjb
  • 1,340
  • 1
  • 13
  • 21
  • It just *might* be worth looking at UDFs : http://www.sqlteam.com/article/user-defined-functions – Fionnuala Nov 24 '10 at 12:30
  • Thanks Remou, I had a quick look but didn't 'get' it fuly. I was thinking of wrapping a function around my jet sql that would convert it to T-SQL on-the-fly just prior to opening a recordset. Is this a similar approach – jjb Nov 24 '10 at 19:37
  • A sproc or udf to do conversion would be very, very painful to write. I wouldn't suggest spending a lot of time looking at that. – Donnie Nov 24 '10 at 21:03
  • Downvoting for not specifying that you're not using Access as the front end. – David-W-Fenton Nov 30 '10 at 00:37
  • It's not uncommon to write front ends in vb/some-other-language and to use jet/some-other-database as the store. My question as written was simply about the differences between jet sql and sql server sql. I had no reason to assume that my choice of front end would have relevance and so I didn't mention it. If I am expected to mention all the things that are not relevant to the question that I am asking where am I to stop? – jjb Nov 30 '10 at 18:11
  • Whatever's common or uncommon, you have to EXPLICITLY STATE YOUR FRONT END. When you just tag as MS-ACCESS and say nothing else, the reasonable assumption is that you're using Access for the front end. You're not, and you should have said so, and not wasted the time of people attempting to address your question in terms that didn't apply. – David-W-Fenton Dec 01 '10 at 02:53
  • I didn't tag the question as MS-Access. AFAIK no one else did either. It's not tagged MS-Access now. – jjb Dec 01 '10 at 15:02

3 Answers3

1

Nope. SQL Server uses T-SQL, and that's not configurable.

Donnie
  • 45,732
  • 10
  • 64
  • 86
1

As far as I can tell, no. Here's a link to a Microsoft migration tool to go from Access to SQL Server: http://www.microsoft.com/sqlserver/2005/en/us/migration-access.aspx.

Share and enjoy.

Aaron Bertrand
  • 272,866
  • 37
  • 466
  • 490
Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
1

I don't understand the question. If you connect to your SQL Server using ODBC linked tables, you'll get what you asked for, i.e., your existing Jet/ACE SQL will work just fine with the SQL Server, because Jet/ACE handles the conversion from Jet/ACE SQL to T-SQL for you.

This is the standard way to upsize, and I can guarantee you that it works.

Now, that said, you'll often run into parts of your app that end up being very slow when you do this, and you'll then need to revise those parts of the app to work more efficiently with the SQL Server. That often entails moving all or part of the offending data retrieval server-side via any of the many options available there (passthrough queries within Access, server-side views that can be used as linked tables, and stored procedures, which are complicated in what you can do with them within Access).

What gets moved server-side will have to have all Access-specific aspects removed, but that mostly entails just using a server-side view without all that, and then using the view in Access and layering the Access-specific stuff on top that way.

But it all depends on the specifics of each problem you're trying to solve. In general, nothing should break at all just because you've converted to SQL Server accessed via ODBC linked tables. That doesn't mean it will be fast, just that it won't break.

David-W-Fenton
  • 22,871
  • 4
  • 45
  • 58
  • Thanks, I had overlooked this as a solution. I had heard of it but have never used linked tables before. The main concern I'd have with this is, as you mention, a slowdown of the application. Do you know of any books/websites that cover this type of upgrade? thanks – jjb Nov 27 '10 at 16:39
  • Chipman and Baron is what I use as my bible on this (Microsoft Access Developer's Guide to SQL Server, http://www.amazon.com/Microsoft-Access-Developers-Guide-Server/dp/0672319446). There shouldn't be any across-the-board performance issues, because Jet/ACE hands off everything it can to the server. However, certain kinds of processes (like a SQL UPDATE statement) can be very inefficient in the way Jet/ACE hands it off. In my experience, you don't acurately forecast what is going to need to be moved server-side until you try it out. – David-W-Fenton Nov 28 '10 at 00:33
  • And, by the way, it's the obvious solution, and the one that Microsoft recommends. There is no alternative except to create an unbound application, and if you do that you might as well quit using Access, since bound data forms are the main advantage of Access. ADPs were an effort to do, er, something, but it didn't work out very well, and MS now deprecates them in favor of MDB/ACCDB with ODBC linked tables. – David-W-Fenton Nov 28 '10 at 00:34
  • Thanks very much. Just one final question. I don't use Access as such, just a jet, .mdb database, the front end is a vb application. I am sorry I didn't spell that out but does what you say still apply or would you follow a different path? – jjb Nov 28 '10 at 09:38
  • Well, yes, it does make all the difference in the world. Nothing I've said here applies to anything but Access connecting to SQL Server. Thanks for wasting my time by omitting crucial information. – David-W-Fenton Nov 30 '10 at 00:36
  • David, thank you for taking the time to answer my question. As it turns out you answered the question you thought I was asking rather than the question I did ask. I was unaware (because I am trying to understand the solution you proposed) until late in the day that this might be the case, but when it became apparent to me, I mentioned it. I have a lot of respect for your knowledge but I honestly think that in this case you are being unreasonable – jjb Nov 30 '10 at 18:20
  • No, I answered one interpretation of your question, which was so poorly worded as to make it unclear what you asked. You thought you were asking one thing, but you weren't -- you were imagining that you were asking a clear question. What is unreasonable is expecting people to GUESS crucial details about your question. – David-W-Fenton Dec 01 '10 at 02:54
  • In the absence of any indication in the question that Access was being used you guessed that Access was being used. – jjb Dec 01 '10 at 15:14
  • Either you read the question and somehow assumed Access was involved or else you guessed that Access was involved. At that time you made no mention of your concerns about the question being badly written. On the basis of your assumption/guess you answered the question a certain way. The only thing that occurred afterwards was for you to discover that your assumption/guess was incorrect. And your response is to get cross with me and downvote the question. Hmmm.. ;) – jjb Dec 01 '10 at 15:24