Onebridge Palm Signatures Fix…

Mar 30
2010

We were having continually and known errors with the onebridge sync process with Palm database files with ink fields. We use them to store the signatures.  This process uses the onebridge backup command which makes a backup of the signature.pdb file on the server and appends the handheld name, date and sync time to give a unique file.

When the sync is complete we send a blank signature pdb file.

From access this function checks the folder for saved pdb files and uses the sat forms SFConvertPDB.exe to make a dbf then loads the signature images into the database.

We have a few local tables we use for our purposes but this code sample would give you a heads up. If you need the sample files register on the site and I will send you the mdb file.

Function LoadData()
 
Dim rst As Recordset, rstSource As Recordset
 
If Me.chkStart Then  ' if ticked start the function

    CurrentDb.Execute ("Delete * from tblFiles") ' clear out the temporary table
    Call ListFilesInFolder(Me.txtLocation) ' loads the name and location of the files
    
    Set rst = CurrentDb.OpenRecordset("select * from tblFiles")
    If rst.RecordCount > 0 Then
        rst.MoveFirst
        Do While Not rst.EOF
            If Len(Dir(Me.txtClearing & "EHCSM0300_TBLSIGN.PDB")) > 0 Then
                'clear existing file
                Kill Me.txtClearing & "EHCSM0300_TBLSIGN.PDB"
            End If
            CurrentDb.Execute ("Delete * from tblsign")
            FileCopy Me.txtLocation & rst!Filename, Me.txtClearing & "EHCSM0300_TBLSIGN.PDB"
 
            Shell "cmd /c" & Me.txtClearing & "LoadSign.bat", vbHide
            Call sSleep(5000)
 
            Set rstSource = CurrentDb.OpenRecordset("tblSign")
            If rstSource.RecordCount > 0 Then
                DoCmd.SetWarnings False
                DoCmd.OpenQuery "qryLoadSign"
                DoCmd.SetWarnings True
            End If
            FileCopy Me.txtLocation & rst!Filename, Me.txtClearing & "\Backup\" & rst!Filename
            Kill Me.txtLocation & rst!Filename
 
        rst.MoveNext
        Loop
    End If
 
    DoCmd.Quit
End If
End Function

And the other function

Function ListFilesInFolder(SourceFolderName As String)
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
Dim rst As Recordset
Set rst = CurrentDb.OpenRecordset("select * from tblFiles") '  'CurrentDb.OpenRecordset("select * from tblFiles")

    Set FSO = New Scripting.FileSystemObject
    Set SourceFolder = FSO.GetFolder(SourceFolderName)
    For Each FileItem In SourceFolder.Files
        If Right(FileItem.Name, 8) = "SIGN.PDB" Then
            rst.AddNew
            rst!Filename = FileItem.Name
            rst!FileLoaded = Now()
            rst.Update
        End If
    Next FileItem
    Set FileItem = Nothing
    Set SourceFolder = Nothing
    Set FSO = Nothing
'    MsgBox "Done"
End Function

Vlookup

Mar 25
2010

I was involved in a discussion on linkedin about vlookup , one of the contributions which I tested simplifies the formula

Instead of
=IF(ISERROR(VLOOKUP(“A”, Table,1,FALSE)),0,VLOOKUP(“A”, table,1,FALSE))
Try
=IFERROR(VLOOKUP(“A”,Table,1, FALSE),0)

Excel Limiting the number of characters

Mar 24
2010

Following from the link on Access this one is for Excel

http://www.techonthenet.com/excel/cells/validation1.php

Who has the database open

Mar 18
2010

Who has the database open , I had a utility from MS a few years ago that listed the users of the database by opening the ldb file. I cant seem to find that program now but there are a number of options some of which I may start to add to future programs.

If you have anything to add to the list please leave a comment and I will update the post

1. An ldb viewer access form from the access web – click here
2. An application posted on Utter Access from Brent – datAdrenaline
3. Explanation of the ldb viewer
4. Explanation of the UserRoster database from microsoft
5. Granite links to open databases and database corruption
6. How to read ldb files

Slow performance on linked tables in Access

Mar 10
2010

I was updating a database designed by a client to improve performance. The table has the Subdatasheet Name property set to [Auto] which was one of the issues.

I had a lot of tables to update and used this code from the microsoft site http://support.microsoft.com/kb/275085

Sub TurnOffSubDataSheets()
Dim MyDB As DAO.Database
Dim MyProperty As DAO.Property
Dim propName As String, propVal As String, rplpropValue As String
Dim propType As Integer, i As Integer
Dim intCount As Integer
 
On Error GoTo tagError
 
Set MyDB = CurrentDb
propName = "SubDataSheetName"
propType = 10
propVal = "[None]"
rplpropValue = "[Auto]"
intCount = 0
 
For i = 0 To MyDB.TableDefs.Count - 1
    If (MyDB.TableDefs(i).Attributes And dbSystemObject) = 0 Then
        If MyDB.TableDefs(i).Properties(propName).Value = rplpropValue Then
             MyDB.TableDefs(i).Properties(propName).Value = propVal
             intCount = intCount + 1
        End If
    End If
tagFromErrorHandling:
Next i
 
MyDB.Close
 
If intCount > 0 Then
    MsgBox "The " & propName & " value for " & intCount & " non-system tables has been updated to " & propVal & "."
End If
 
Exit Sub
 
tagError:
If Err.Number = 3270 Then
    Set MyProperty = MyDB.TableDefs(i).CreateProperty(propName)
    MyProperty.Type = propType
    MyProperty.Value = propVal
    MyDB.TableDefs(i).Properties.Append MyProperty
    intCount = intCount + 1
    Resume tagFromErrorHandling
Else
    MsgBox Err.Description & vbCrLf & vbCrLf & " in TurnOffSubDataSheets routine."
End If
End Sub

Limiting the length of an unbound field in Access

Mar 02
2010

Saw this on everythingaccess.com very useful..

Browser testing

Mar 02
2010

This is a useful site for testing your web design in different browsers

http://ipinfo.info/netrenderer/

If you need some help on a project drop leave a comment on the post and I will reply.