• Home
  • Blog
  • Resume
  • Contact
  • Projects
  • Gallery
  • Amit’s Resume
  • About Nagpur
KEEP IN TOUCH

Posts tagged Access

Excel Books

Sep06
2007
Leave a Comment Written by admin

Books on my shelf:
Professional Excel Development by Stephen Bullen, Rob Bovey

This is by far the most authoritative book on Excel development from Add-ins to APIs. A word of caution though, it is not for the beginners.

Excel Advanced Report Development by Timothy Zapawa

Ever wondered on using Excel as a report development tool? This might be an answer to it; however, my problem with this book is that it focuses too much on pivottables, and because of that it doesn?t do justification to the name of the book. At any rate, this is very good book to exploit pivottable to its limits.

Excel 2007 Power Programming with VBA by John Walkenbach

Of course, it cannot happen that you talk about Excel books and don?t mention Mr. Spreadsheet himself. I am his fan on personal and professional level. On personal level, I love his blog, and on professional level, I like his lucid language in his books. I recommend reading his books and blog both.
Excel 2003 Formulas by John Walkenbach

You thought you knew formulas? Just read this book and you?ll realize, which I do almost every day, that there just so much about Excel that we don?t know. John uses simple language, and introduces the readers from basic to advanced formulas. How advance you might ask, well, here is an example, which uses a formula similar to this:

Excel 2007 Formulas by John Walkenbach
Books I have Read:
Data Analysis and Decision Making with Microsoft Excel by S. Christian Albright

I borrowed this book from the library. It is a very good for beginners.

Excel 2003 Power Programming with VBA by John Walkenbach
Integrating Excel and Access by Michael Schmalz

I borrowed this book from the library too. Excellent reference on developing applications using Access, Excel, and VBA.
Mr Excel ON EXCEL by Bill Jelen

This is my first book I read on Excel, which is written by Bill Jelen aka Mr. Excel. Fantastic book. If you have begun knowing Excel just now, obtain a copy of this book right away. This is the right start for beginners. It uses very simple and clear language, and Bill provides great examples to make the learning useful.

Guerilla Data Analysis Using Microsoft Excel by Bill Jelen

I had an e-version of this book, but for some reason it’s not working anymore. I had chewed on this book a lot. It?s a great book for starters, especially, in data crunching.

Posted in Books - Tagged Bill Jelen, John Walkenbach, VBA

Access VBA: Convert Access tables to arff format

Jul18
2007
2 Comments Written by admin

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

Posted in Useful Procedures - Tagged VBA

Access VBA: Export Access tables using ODBC

Jul18
2007
3 Comments Written by admin

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

Posted in Useful Procedures - Tagged ODBC, VBA

Access VBA: Delete tables from Access database

Jul18
2007
Leave a Comment Written by admin

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

Posted in Useful Procedures - Tagged VBA

Access VBA: Link all Dbase files from a folder

Jul18
2007
Leave a Comment Written by admin

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

Posted in Useful Procedures - Tagged dbase, VBA
« Older Entries Newer Entries »

Tags

Access Alt F8 Books boxplot cells charts count cursor dashboard data mining dbase design error excel excel functions export filter flip LaTex MS query Number Err ODBC pipes Press Alt F11 Public Sub python R random numbers Range Cells report scripting software sparklines SQL SQL server stack columns statistics stemming string tag cloud text mining UDF VBA visualization wildcard

Network

View Ashutosh Nandeshwar's profile on LinkedIn

Recent Comments

  • larry on Access Export to Excel (2007)
  • Betty Chou on Projects
  • Rwill on Access Export to Excel (2007)
  • Bharathi on The search key was not found in any record in Access
  • Michael on The search key was not found in any record in Access

EvoLve theme by Blogatize  •  Powered by WordPress nandeshwar.info