' vi: foldmethod=marker foldmarker={{{,}}}
'
' Adding ADODB reference to VBA project:
'
' ' Microsoft ActiveX Data Objects 2.8 Library
' thisWorkbook.VBProject.references.addFromGuid guid := "{2A75196C-D9EB-4129-B803-931327F72D5C}", major := 0, minor := 0
'
' ' Microsoft ActiveX Data Objects 6.1 Library
' thisWorkbook.VBProject.references.addFromGuid guid := "{B691E011-1797-432E-907A-4D8C69339129}", major := 6, minor := 1
'
option explicit
' copyExcelSheetToNewAccessTable {{{
public sub copyExcelSheetToNewAccessTable( _
excelFile as string, _
sheetName as string, _
accessFile as string, _
newTableName as string)
' Compare ..\Access\CommonFunctionalityDB.bas -> importExcelDataIntoTable
dim con as ADODB.connection
set con = openADOConnectionToExcelFile(excelFile, false)
con.execute("select * into [" & newTableName & "] in '" & accessFile & "' from [" & sheetName & "$]")
end sub ' }}}
' copyAccessTableToNewAccessTable {{{
public sub copyAccessTableToNewAccessTable ( _
accessFileFrom as string, _
tableNameFrom as string, _
accessFileTo as string, _
tableNameTo as string)
' Compare ..\Access\CommonFunctionalityDB.bas -> importExcelDataIntoTable
dim con as new ADODB.connection
set con = openADOConnectionToAccess(accessFileFrom)
con.execute("select * into [" & tableNameTo & "] in '" & accessFileTo & "' from [" & tableNameFrom & "]")
end sub ' }}}
' openADOConnectionToOracle {{{
public function openADOConnectionToOracle( _
dbUser as string, _
dbPassword as string, _
dbName as string) as ADODB.connection
on error GoTo error_handler
' Dim cn As ADODB.Connection
' set cn = New ADODB.Connection
set openADOConnectionToOracle = new ADODB.connection
openADOConnectionToOracle.open ( _
"User ID=" & dbUser & _
";Password=" & dbPassword & _
";Data Source=" & dbName & _
";Provider=MSDAORA.1")
' set openADOConnectionToOracle = cn
exit function
error_handler:
if err.number = -2147467259 Then
msgBox ("Error opening connection to Oracle: " & err.description)
else
msgBox (err.Number & " " & err.Description)
end if
end function ' }}}
' openADOConnectionToSQLServer {{{
public function openADOConnectionToSQLServer( _
dbInstance as string, _
dbName as string) as ADODB.connection
on error GoTo error_handler
set openADOConnectionToSQLServer = new ADODB.connection
openADOConnectionToSQLServer.open ( _
"Provider=SQLOLEDB;" & _
"Data Source=" & dbInstance & ";" & _
"Initial Catalog=" & dbName & ";" & _
"Integrated Security=SSPI;")
exit function
error_handler:
if err.number = -2147467259 Then
msgBox ("Error opening connection to SQL Server: " & err.description)
else
msgBox (err.Number & " " & err.Description)
end if
end function ' }}}
public function openADOConnectionToAccess(accessFile as string) as ADODB.connection ' {{{
set openADOConnectionToAccess = new ADODB.connection
openADOConnectionToAccess.open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & accessFile & "'"
end function ' }}}
public function openADOConnectionToExcelFile(excelFile as string, optional header as boolean = true) as ADODB.connection ' {{{
set openADOConnectionToExcelFile = new ADODB.connection
openADOConnectionToExcelFile.connectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source='" & excelFile & "'"
if header then
openADOConnectionToExcelFile.connectionString = openADOConnectionToExcelFile.connectionString & ";Extended Properties=""Excel 8.0;HDR=yes;IMEX=1;"""
else
openADOConnectionToExcelFile.connectionString = openADOConnectionToExcelFile.connectionString & ";Extended Properties=""Excel 8.0;HDR=no;IMEX=1;"""
end if
openADOConnectionToExcelFile.open
end function ' }}}
' ADODoesTableExist {{{
public function ADODoesTableExist( _
con as ADODB.connection, _
tableName as string _
) as boolean
' http://stackoverflow.com/a/1082482/180275
dim rsSchema As ADODB.recordset
Set rsSchema = con.openSchema(adSchemaColumns, array(empty, empty, tableName, empty))
if rsSchema.BOF And rsSchema.eof then
ADODoesTableExist = false
else
ADODoesTableExist = true
end if
rsSchema.Close
set rsSchema = Nothing
end function ' }}}
public function ADOExecuteSQL(con as ADODB.connection, stmt as string) as long ' {{{
on error goto nok
call con.execute(stmt, ADOExecuteSQL)
exit function
nok:
call err.raise(1000 + vbObjectError, "ADOHelper.bas - ADOExecuteSQL", err.description & " [" & err.number & "]"& vbCrLf & "stmt = " & stmt)
end function ' }}}
public function ADOSelect1R1C(con as ADODB.connection, stmt as string) as variant ' {{{
dim rs as ADODB.recordset
set rs = con.execute(stmt)
' if not rs.eof then
' rs.moveNext
ADOSelect1R1C = rs.fields(0).value
' end if
rs.close
end function ' }}}
public function createSelectStatementFromFile(con as ADODB.connection, filename as string) as adoSelectStatement ' {{{
dbg_.indent "adoHelpers.createSelectStatementFromFile, filename = " & filename
set createSelectStatementFromFile = new adoSelectStatement
call createSelectStatementFromFile.init(con)
createSelectStatementFromFile.sqlFromFile(filename)
dbg_.dedent
end function ' }}}