Erlandsen Data ConsultingBelow you will find example ADO connection strings that you can use when connecting to some of the most common database types. Connecting to any data source using an ODBC DSN (Data Source Name): You must first create the DSN, this can be done using the "ODBC Data Source Administrator" found in "Control Panel" or [...]
Updating a database with new records using separate INSERT commands for each new record can sometimes take very long time, especially if there are many thousands of new records to insert. One way to speed up this process can be to use the BULK INSERT command to update the SQL database in a significantly quicker [...]
If you want to export data to an Access table from an Excel worksheet, the macro example below shows how this can be done: Sub ADOFromExcelToAccess() ‘ exports data from the active worksheet to a table in an Access database ‘ this procedure must be edited before use Dim cn As ADODB.Connection, rs As ADODB.Recordset, [...]
The procedure below can be used to create a dummy text file containing 1 000 000 semi-colon separated records: Sub CreateTextFileDB() Dim strTextFile As String, f As Integer Dim strItem1 As String, strItem2 As String Dim i As Long, j As Long strTextFile = “C:Temp” & Format(Date, “yyyymmdd”) & “.txt” On Error Resume Next Kill [...]
The procedure below can be used to get an ADO recordset from a text file and fill in the result in a worksheet. Sub GetTextFileData(strSQL As String, strFolder As String, rngTargetCell As Range) ‘ example: GetTextFileData “SELECT * FROM filename.txt”, “C:\FolderName”, Range(“A3″) ‘ example: GetTextFileData “SELECT * FROM filename.txt WHERE fieldname = ‘criteria’”, “C:\FolderName”, Range(“A3″) [...]
If you don’t want to use the CopyFromRecordset method (Excel 2000 or later) you can use the procedure below to transfer data from a recordset to a worksheet. After you have connected to a database and retrieved data into a recordset variable you can use the procedure RS2WS to transfer the contents to a worksheet, [...]
With the procedure below you can import data from an Access table to a worksheet. Sub ADOImportFromAccessTable(DBFullName As String, TableName As String, TargetRange As Range) ‘ Example: ADOImportFromAccessTable "C:\FolderName\DataBaseName.mdb", "TableName", Range("C1") Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer Set TargetRange = TargetRange.Cells(1, 1) ‘ open the database Set cn = New ADODB.Connection [...]
With the procedures below you can use ADO to retrieve a recordset from a closed workbook and read/write data. In the example below, replace SheetName with the worksheet name you want to retrieve data from. ‘ call the procedure like this: ‘ GetWorksheetData “C:\Foldername\Filename.xls”, “SELECT * FROM [SheetName$];”, ThisWorkbook.Worksheets(1).Range(“A3″) Sub GetWorksheetData(strSourceFile As String, strSQL As [...]
If you want to import a lot of data from a closed workbook you can do this with ADO and the macro below. If you want to retrieve data from another worksheet than the first worksheet in the closed workbook, you have to refer to a user defined named range. The macro below can be [...]