Using bit mask fields with hexadecimal in MS Access

davidgaven · October 16th, 2010, 05:23 PM

I have a MS Access database connected to SQL via ODBC. I am having difficulties with how to use the following SQL in MS Access. Can MS Access do this, I have seen the CInt function - not sure if this will work.

Code:

CASE WHEN (CAST(C5.linternal AS BIGINT) & CAST(0x00000100 AS BIGINT) <> 0 
       AND   CAST(C5.linternal AS BIGINT) & CAST(0x00000002 AS BIGINT) = 0) THEN 'Closed'
       WHEN (CAST(C5.linternal AS BIGINT) & CAST(0x00000040 AS BIGINT) <> 0 
       OR    CAST(C5.linternal AS BIGINT) & CAST(0x00000002 AS BIGINT) <> 0) THEN 'Charged'
       WHEN CAST(C5.linternal AS BIGINT) & CAST(0x00000001 AS BIGINT) <> 0 THEN 'Done'
     WHEN CAST(C5.linternal AS BIGINT) & CAST(0x00010000 AS BIGINT) <> 0 THEN 'Inactive'
       ELSE 'Planned' END AS COTStatus,

Thanks for any help or thoughts.
David

gbianchi · October 16th, 2010, 07:04 PM

Cast doesn't exists on access.. how are you storing this data on the access field???

davidgaven · October 16th, 2010, 07:13 PM

Thanks for the reply.

I know that Cast doesn't existing in MS Access. The code I have is in MS SQL Server, and I need to perform the same in MS Access.

David

HiTechCoach · October 16th, 2010, 07:23 PM

If you are using Access as a front end to a SQL Server back end , think that is wnat you meant by "connected to SQL (Server) via ODBC"), then have you tried usng a Pass-Through Query (Click here).

davidgaven · October 16th, 2010, 07:43 PM

Boyd,

Thanks for the reply. Yes correct, MS Access front end using an ODBC link to a SQL Server database.

I had always seen the Pass-Through Query but paid no attention to it. I have accessed the link you provided, I think this may be just the ticket.

This may also solve another problem I have with the limitations of columns in the Jet Database. Normally I overcome this by creating a view in MS SQL, and then access the view in MS Access.

Thanks again, I shall give this a go.

David

HiTechCoach · October 16th, 2010, 07:54 PM

David,

You're welcome.

Hope that helps you with your project.

Old Pedant · October 18th, 2010, 03:13 PM

FWIW, this *can* be done in Access. Well, except that Access doesn't support BIGINT.

Code:

IIF( CLNG(C5.linternal) AND &H00000102 = &H00000100, 'Closed,
IIF( CLNG(C5.linternal) AND &H00000042 <>         0, 'Charged',
IIF( CLNG(C5.linternal) AND &H00010001 <>         0, 'Done',
IIF( CLNG(C5.linternal) AND &H00010000 <>         0, 'Inactive',
                                                     'Planned' 
) ) ) ) AS COTStatus,

Note that the first two lines there suggest that the T-SQL code can be simplified in the same way.

davidgaven · October 20th, 2010, 06:55 PM

Old Pedant,

Thanks very much for your reply, and sorry for the delay getting back to you. This looks like a great solution. Unfortunately, I have been working on another problem.

I have created the MS Access application and ready to deploy it to users. I have done this before at another organisation and it has been working perfectly.

I usually do it by creating a System DSN and using a windows authenticated SQL user.

My challenge at this new organisation is that I am creating a reporting application in MS Access, and I want users to have read only permissions. Sounds easy I know, however every user has a SQL login on the SQL Server that provides read-write access. So I created a generic login, and set-up a system DSN that uses SQL authentication.

The application uses linked tables, and has given users read-write permissions to tables. It appears that the windows authentication is taking preference over the SQL authentication.

Oh, and the other problem is that users do not have admin rights on the PC, so I need the IT administrator to create the DSN each time - that is why I want a DSN less connection.

I have searched around the web looking for code over the last few days, and have found the following:

Code:

Global ODBC1 As String
Global ODBC2 As String
Global ODBC3 As String
Global ODBC4 As String

Function InitApp()
ODBC1 = "????.edu.au"
ODBC2 = "?????_Production"
ODBC3 = "Report_Reader"
ODBC4 = "?????1234"
End Function

Function RelinkTables() As Boolean
Call RefreshODBCLinks("ODBC;Description=Optional Description Here;DRIVER=SQL Server;" & _
"SERVER=" & ODBC1 & ";UID=" & ODBC3 & ";" & _
"PWD=" & ODBC4 & ";DATABASE=" & ODBC2 & ";LANGUAGE=us_english")

End Function

Public Sub RefreshODBCLinks(newConnectionString As String)
On Error GoTo RODBCL_ERR
Dim db As DAO.Database
Dim tb As DAO.TableDef
Set db = CurrentDb
For Each tb In db.TableDefs
If Left(tb.Connect, 4) = "ODBC" Then
tb.Connect = newConnectionString
tb.RefreshLink
Debug.Print "Refreshed ODBC table " & tb.Name
End If
Next tb
Set db = Nothing
Exit Sub
RODBCL_ERR:
MsgBox "Error during table link occured", vbCritical, "Error Quitting"
Quit
End Sub

This runs in the autoexec when the application opens.

I don't really want to refresh all the linked tables as it takes a little time to run, but not sure if there is an easier way?

Regards
David

HiTechCoach · October 20th, 2010, 09:13 PM

David,

Have you tried this:

Using DSN-Less Connections

I have tested it and it works great.

davidgaven · October 20th, 2010, 11:39 PM

Hi Boyd,

Thanks again for the reply.

I had a look at this code and noticed it was using windows authentication which I didn't want. It did mention about the change needed to use SQL authentication, but unfortunately my skills are very limited.

Anyway, finally found something that is working and working very well.

Code:

'//Name     :   AttachDSNLessTable
'//Purpose  :   Create a linked table to SQL Server without using a DSN
'//Parameters
'//     stLocalTableName: Name of the table that you are creating in the current database
'//     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
'//     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
      
   '//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
   
    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

There are only five tables, and I call the procedure five times. It is very fast and doesn't need DSN connected.

Again thanks for your reply.

David