Bytes - insights http://bytes.com en Sat, 21 Nov 2009 01:05:02 GMT vBulletin 60 http://bytes.com/images/misc/rss.jpg Bytes - insights http://bytes.com Relinking odbc tables using VBA http://bytes.com/topic/access/insights/876985-relinking-odbc-tables-using-vba Fri, 13 Nov 2009 15:07:49 GMT As a lot of my projects involve using an odbc linked backend data source I have found this piece of code to be very useful. I usually trigger it to... As a lot of my projects involve using an odbc linked backend data source I have found this piece of code to be very useful. I usually trigger it to run on system startup like in AutoExec macro. It avoids any difficulties down the road with broken links and if the DSN name or database server changes then the only edit that needs to be made is to the connection string.

Expand|Select|Wrap|Line Numbers
  1. Function relinkTables()
  2. Dim tdf As DAO.TableDef
  3.  
  4.     For Each tdf In CurrentDb.TableDefs
  5.         ' check if table is a linked table
  6.         If Len(tdf.Connect) > 0 Then
  7.             tdf.Connect = "odbc connection string to the DSN or database"
  8.             tdf.RefreshLink
  9.         End If
  10.     Next
  11.  
  12. End Function
  13.  
Note: If you have other linked tables aside from those connected by odbc you would have to allow for them in the code.

As usual all advice, critique and enhancements welcome :)

Mary ]]> insights msquared http://bytes.com/topic/access/insights/876985-relinking-odbc-tables-using-vba Format system time to milliseconds in VBA http://bytes.com/topic/access/insights/876984-format-system-time-milliseconds-vba Fri, 13 Nov 2009 14:54:25 GMT I found I needed this recently when creating a file which had to be uploaded by ftp. The file extension was a timestamp of hhnnssms and the ms part... I found I needed this recently when creating a file which had to be uploaded by ftp. The file extension was a timestamp of hhnnssms and the ms part was formatted to 4 digits. As the Now() function doesn't return milliseconds I used the following function to calculate it.

Expand|Select|Wrap|Line Numbers
  1. Option Compare Database
  2. Option Explicit
  3.  
  4. Private Type SYSTEMTIME
  5. wYear As Integer
  6. wMonth As Integer
  7. wDayOfWeek As Integer
  8. wDay As Integer
  9. wHour As Integer
  10. wMinute As Integer
  11. wSecond As Integer
  12. wMilliseconds As Integer
  13. End Type
  14.  
  15.  
  16. Private Declare Sub GetSystemTime Lib "kernel32" _
  17. (lpSystemTime As SYSTEMTIME)
  18.  
  19. Public Function TimeToMillisecond() As String
  20. Dim tSystem As SYSTEMTIME
  21. Dim sRet
  22.  
  23.     On Error Resume Next
  24.  
  25.     GetSystemTime tSystem
  26.  
  27.     sRet = Hour(Now) & Minute(Now) & Second(Now) & Format(tSystem.wMilliseconds, "0000")
  28.  
  29.     TimeToMillisecond = sRet
  30.  
  31. End Function
  32.  
As usual all advice, critique and enhancements welcome :)

Mary ]]> insights msquared http://bytes.com/topic/access/insights/876984-format-system-time-milliseconds-vba Using msoFileDialogSaveAs in MS Access http://bytes.com/topic/access/insights/876982-using-msofiledialogsaveas-ms-access Fri, 13 Nov 2009 14:44:43 GMT I recently had occasion to want to export the results of a query from Access to Excel using FileDialog. I found researching it that there was a lot... I recently had occasion to want to export the results of a query from Access to Excel using FileDialog. I found researching it that there was a lot of confusion regarding whether SaveAs could even be used in Access and couldn't find a simple routine. I experimented a little and came up with the following which works perfectly.

Although I used this routine to export to Excel it could in theory be used with any format supported by DoCmd.Output or really any statement which output a file.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdSendtoExcel_Click()
  2. Dim fd As FileDialog
  3. Dim Title As String
  4. Dim vrtSelectedItem As Variant
  5.  
  6.     Set fd = Application.FileDialog(msoFileDialogSaveAs)
  7.  
  8.     With fd
  9.         .AllowMultiSelect = False
  10.         .Title = "Save File"
  11.         .InitialFileName = "Name Of Report " & Format(Now(), "ddmmyyyyhhnn") & ".xls"
  12.  
  13.         If .Show = True Then
  14.             For Each vrtSelectedItem In .SelectedItems
  15.                 DoCmd.OutputTo acOutputQuery, "query or table name", acFormatXLS, vrtSelectedItem
  16.             Next vrtSelectedItem
  17.         Else
  18.             MsgBox "No file was selected"
  19.         End If
  20.  
  21.     End With
  22.  
  23. End Sub
  24.  
If anyone knows of any improvedments or enhancements to this routine feel free to offer advice.

Mary ]]> insights msquared http://bytes.com/topic/access/insights/876982-using-msofiledialogsaveas-ms-access