Wednesday, July 18, 2007
Access VBA: Convert Access tables to arff format
Weka, an open source data mining software, uses arff input data format. You can use this code to convert any Access table to arff format. Download the Access database with code: Convert2Arff.mdb
This can very well be designed using forms, but this should get one started. Use Alt + F11 to see the code, then execute procedure ConvertTbl2Arff to convert a table.
Some highlights of this procedure
- Takes care of spaces in Attribute name and data values
- Finds unique values of nominal variables
- Assigns equivalent ARFF datatype
- Replaces missing values with question marks
This can very well be designed using forms, but this should get one started. Use Alt + F11 to see the code, then execute procedure ConvertTbl2Arff to convert a table.
Some highlights of this procedure
- Takes care of spaces in Attribute name and data values
- Finds unique values of nominal variables
- Assigns equivalent ARFF datatype
- Replaces missing values with question marks
Labels: Access
Access VBA: Export Access tables using ODBC
If you want to export Access tables using ODBC/DSN connections, use the following code. This procedure uses the File DSN and ODBC connection to export Access tables using DAO object TableDef.
Sub ExportTbls()
Dim sTblNm As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim db As Database, tbldef As DAO.TableDef
On Error GoTo ExportTbls_Error
sTypExprt = "ODBC Database" 'Export Type
sCnxnStr = "ODBC;DSN=DSNName;UID=userID;PWD=password" 'Create the connection string
vStTime = Timer
Application.Echo False, "Visual Basic code is executing."
Set db = CurrentDb()
'need a reference to Microsoft DAO 3.x library
For Each tbldef In db.TableDefs
'Don't export System and temporary tables
If Left(tbldef.Name, 4) <> "MSys" And Left(tbldef.Name, 4) <> "~TMP" Then
'Debug.Print tbldef.Name
sTblNm = tbldef.Name
DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
End If
Next tbldef
MsgBox "Done! Time taken=" & Timer - vStTime
On Error GoTo 0
SmoothExit_ExportTbls:
Set db = Nothing
Application.Echo True
Exit Sub
ExportTbls_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportTblsODST"
Resume SmoothExit_ExportTbls
End Sub
Dim sTblNm As String
Dim sTypExprt As String
Dim sCnxnStr As String, vStTime As Variant
Dim db As Database, tbldef As DAO.TableDef
On Error GoTo ExportTbls_Error
sTypExprt = "ODBC Database" 'Export Type
sCnxnStr = "ODBC;DSN=DSNName;UID=userID;PWD=password" 'Create the connection string
vStTime = Timer
Application.Echo False, "Visual Basic code is executing."
Set db = CurrentDb()
'need a reference to Microsoft DAO 3.x library
For Each tbldef In db.TableDefs
'Don't export System and temporary tables
If Left(tbldef.Name, 4) <> "MSys" And Left(tbldef.Name, 4) <> "~TMP" Then
'Debug.Print tbldef.Name
sTblNm = tbldef.Name
DoCmd.TransferDatabase acExport, sTypExprt, sCnxnStr, acTable, sTblNm, sTblNm
End If
Next tbldef
MsgBox "Done! Time taken=" & Timer - vStTime
On Error GoTo 0
SmoothExit_ExportTbls:
Set db = Nothing
Application.Echo True
Exit Sub
ExportTbls_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure ExportTblsODST"
Resume SmoothExit_ExportTbls
End Sub
Labels: Access
Access VBA: Delete tables from Access database
If you want to delete all or some of the tables from your Access database, you can use this DAO approach. You would need a reference to Microsoft DAO 3.x object library. As shown in the example, you can use an array to store the table names, which you want to keep or delete.
Sub DelteTbls()
Dim sTblNm As String
Dim db As Database, tbldef As DAO.TableDef
Dim i As Integer, Arr As Variant
On Error GoTo DelteTbls_Error
'You can use an array if you want to delete or not delete specific tables
'Arr = Array("Table1","Table2","Table3")
Set db = CurrentDb() 'Set the database object
'Set the warnings off to suppress messages
DoCmd.SetWarnings False
'For i = 0 To UBound(Arr)
For Each tbldef In db.TableDefs
'here you can use equal to or not equal to delete or keep specific tables
'If Left(tbldef.Name, 4) = Arr(i) Then
'Don't delete System or temporary tables
If Left(tbldef.Name, 4) <> "MSys" And Left(tbldef.Name, 1) <> "~" Then
Debug.Print tbldef.Name
sTblNm = tbldef.Name
'Delete table
DoCmd.DeleteObject acTable, sTblNm
End If
Next tbldef
'Next i
MsgBox "Done!"
On Error GoTo 0
SmoothExit_DelteTbls:
Set db = Nothing
DoCmd.SetWarnings True
Exit Sub
DelteTbls_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DelteTbls"
Resume SmoothExit_DelteTbls
End Sub
Dim sTblNm As String
Dim db As Database, tbldef As DAO.TableDef
Dim i As Integer, Arr As Variant
On Error GoTo DelteTbls_Error
'You can use an array if you want to delete or not delete specific tables
'Arr = Array("Table1","Table2","Table3")
Set db = CurrentDb() 'Set the database object
'Set the warnings off to suppress messages
DoCmd.SetWarnings False
'For i = 0 To UBound(Arr)
For Each tbldef In db.TableDefs
'here you can use equal to or not equal to delete or keep specific tables
'If Left(tbldef.Name, 4) = Arr(i) Then
'Don't delete System or temporary tables
If Left(tbldef.Name, 4) <> "MSys" And Left(tbldef.Name, 1) <> "~" Then
Debug.Print tbldef.Name
sTblNm = tbldef.Name
'Delete table
DoCmd.DeleteObject acTable, sTblNm
End If
Next tbldef
'Next i
MsgBox "Done!"
On Error GoTo 0
SmoothExit_DelteTbls:
Set db = Nothing
DoCmd.SetWarnings True
Exit Sub
DelteTbls_Error:
MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure DelteTbls"
Resume SmoothExit_DelteTbls
End Sub
Labels: Access
Access VBA: Link all Dbase files from a folder
If you would like to link all Dbase files, any linkable file for that matter, in MS Access, use the following code. I read somewhere that refreshing the links is slower than deleting and creating new links.
Sub LinkAllTblsinDir()
Dim sTblNm As String, sPath As String, sFileNm As String
sPath = "C:\DW\"
'Turn of the Echo to avoid window repaint/refresh
Application.Echo False
sFileNm = Dir(sPath, vbNormal)
Do While sFileNm <> ""
If Right(sFileNm, 3) = "dbf" Then
sTblNm = Left(sFileNm, Len(sFileNm) - 4) 'Extract the file name
'Use the TransferDatabase option to link the tables from the specified directory
'to your current Access DB
DoCmd.TransferDatabase acLink, "dBase III", sPath, acTable, sTblNm, sTblNm
End If
sFileNm = Dir
Loop
Application.Echo True
End Sub
Dim sTblNm As String, sPath As String, sFileNm As String
sPath = "C:\DW\"
'Turn of the Echo to avoid window repaint/refresh
Application.Echo False
sFileNm = Dir(sPath, vbNormal)
Do While sFileNm <> ""
If Right(sFileNm, 3) = "dbf" Then
sTblNm = Left(sFileNm, Len(sFileNm) - 4) 'Extract the file name
'Use the TransferDatabase option to link the tables from the specified directory
'to your current Access DB
DoCmd.TransferDatabase acLink, "dBase III", sPath, acTable, sTblNm, sTblNm
End If
sFileNm = Dir
Loop
Application.Echo True
End Sub
Labels: Access
Friday, November 04, 2005
XY scatter plot in MS Access
Although, this is an Access entry but this text is Google serachble so someone who is looking for this can find it, therefore I'm posting it here.
I've created animation of plotting a XY scatter in MS Access and placed here.
Also, I've described the procedure here.
I've created animation of plotting a XY scatter in MS Access and placed here.
Also, I've described the procedure here.
Labels: Access
