Sql to Access Translation - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Sql to Access Translation

Author

Topic

Kevin Mesiab
Starting Member

3 Posts

Posted - 2005-05-27 : 19:47:42

Hi,

I have the following SQL statement that I've been using with MSDE with good results. My new requirements are to convert it to work with Access. I am not as familiar with Access syntax and am having trouble finding information to aid me here.

Thanks in advance for your help.

Here is the statement:


        if(
            not exists(
                select
                    'true'
                from
                    Host
                where
                    Mac = '00:01:E6:A2:00:00'
                )
            )
            begin

                /* insert this record */
                insert into Host
                (
                    Mac,
                    IPAddress,
                    Hostname,
                    Model,
                    Description,
                    CustomerId,
                    DealerId
                )
                values
                (
                    '00:01:E6:A2:00:00',
                    '192.168.1.102',
                    'Legal Dept',
                    'HP LaserJet 4100 MFP',
                    'HP LaserJet 4100 MFP',
                    'Some Customer',
                    'Some Dealer'
                )

            end

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-05-31 : 13:53:50

Kevin, I'm surprised you are moving from MSDE to Access. I always considered that down-grading an application. But hey, whatever you choose (or are instructed to do).

To answer your question, I'm not sure there is a direct corresponding single instruction in Access. At least, I have never had any luck getting Access to do anything like that (IF...BEGIN). You may have to split it into two pieces either in macro or module code.

---------------------------
EmeraldCityDomains.com

Kevin Mesiab
Starting Member

3 Posts

Posted - 2005-06-01 : 02:13:24

Thanks for the reply. It most certainly is a downgrade for customers with tighter purse strings. Plus it's more likely that a user base has staff who are familiar with the Access interface than with reporting tools for SQL Server, let alone a server license.

As far as the SQL goes, I could not find anything in terms of Access SQL syntax to give me what I wanted. I was almost tempted to change (read:break) the application interface to solve the problem programmatically but, alas, I did not.

Here is how I solved it in case anyone else has a similar issue:

I placed a UNIQUE constraint on the field in the database and perform a straight insert from the client. If the insert fails I ignore the error message returned by the driver and proceed. Not the most elegant hack, but it works while preserving the interfaces.

-Kevin Mesiab

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2005-06-01 : 14:09:55

Glad you found a solution that works for you. But I'm curious about the comment regarding customers with tighter purse strings. The last I checked, MSDE was a freely distributable database engine not requiring a server license. But if your clients are doing their own ad-hoc reporting, which you seem to suggest, I can definitely see putting them into Access for simplicity. I just wanted to hightlight MSDE as an inexpensive option because so many people seem to overlook it and then suffer massive headaches wanting Access to behave like SQL Server.

---------------------------
EmeraldCityDomains.com

Kevin Mesiab
Starting Member

3 Posts

Posted - 2005-06-02 : 22:31:48

quote:
Originally posted by AjarnMark

MSDE was a freely distributable database engine not requiring a server license.

That it is, however, redistributing MSDE adds complexity during the installation phase. The primary reason though, as I mentioned and you pointed out, is the simplicity of ad-hoc reporting.

-Kevin Mesiab

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources