S
StumpedAgain
My company uses oracle databases. After I set up the connection via Data
Sources (ODBC), what is the best way to access information from the database
using VBA? I used to have the database locally, but I want to make it
available to multiple users. I've recorded an "import external data" sub,
but I can't make heads or tails out of the code. What's the best way to do
this? Thanks for any help!
What I had:
GetDBLocation = Worksheets("SETTINGS").Range("T2").Value
Set InitDatabase = OpenDatabase(GetDBLocation)
Recorded macro from Import External Data->New Database Query (mind wrapping):
Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=OSM;UID=OKJ/OKJ;;DBQ=CPIDOSMP;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=N" _
), Array("LS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;")),
Destination:= _
Range("A1"))
.CommandText = Array( _
"SELECT AK_FOREIGN_KEY_COLUMNS_V.ROW_ID" & Chr(13) & "" & Chr(10) &
"FROM TKR.AK_FOREIGN_KEY_COLUMNS_V AK_FOREIGN_KEY_COLUMNS_V" & Chr(13) & "" &
Chr(10) & "ORDER BY AK_FOREIGN_KEY_COLUMNS_V.ROW_ID" _
)
.Name = "Query from OSM"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub
Sources (ODBC), what is the best way to access information from the database
using VBA? I used to have the database locally, but I want to make it
available to multiple users. I've recorded an "import external data" sub,
but I can't make heads or tails out of the code. What's the best way to do
this? Thanks for any help!
What I had:
GetDBLocation = Worksheets("SETTINGS").Range("T2").Value
Set InitDatabase = OpenDatabase(GetDBLocation)
Recorded macro from Import External Data->New Database Query (mind wrapping):
Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:=Array(Array( _
"ODBC;DSN=OSM;UID=OKJ/OKJ;;DBQ=CPIDOSMP;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FRC=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSuccessful;NUM=N" _
), Array("LS;DPM=F;MTS=T;MDI=F;CSR=F;FWC=F;FBS=64000;TLO=O;")),
Destination:= _
Range("A1"))
.CommandText = Array( _
"SELECT AK_FOREIGN_KEY_COLUMNS_V.ROW_ID" & Chr(13) & "" & Chr(10) &
"FROM TKR.AK_FOREIGN_KEY_COLUMNS_V AK_FOREIGN_KEY_COLUMNS_V" & Chr(13) & "" &
Chr(10) & "ORDER BY AK_FOREIGN_KEY_COLUMNS_V.ROW_ID" _
)
.Name = "Query from OSM"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.Refresh BackgroundQuery:=False
End With
End Sub