Link to database
 

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(Arra y( _

"ODBC;DSN=OSM;UID=OKJ/OKJ;;DBQ=CPIDOSMP;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FR C=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSucce ssful;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
--
-SA

Link to database
 

Maybe I am missing the point, but couldn't you just refresh the query rather
than recreating it each time?

Something like:

Selection.QueryTable.Refresh BackgroundQuery:=False

Tom

"StumpedAgain" wrote:

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(Arra y( _

"ODBC;DSN=OSM;UID=OKJ/OKJ;;DBQ=CPIDOSMP;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FR C=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSucce ssful;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
--
-SA

Link to database
 

Hi Tom thanks for the reply.

I /think/ what I'm after is the way in which I point to the database. Once
I have a connection, I can use SQL to grab what I want, but getting to that
point is what I'm stuck on. I'm new to this aspect of VBA so I appreciate
any insight/direction that you have to offer.

How would I use your suggestion?
--
-SA


"TomPl" wrote:

Maybe I am missing the point, but couldn't you just refresh the query rather
than recreating it each time?

Something like:

Selection.QueryTable.Refresh BackgroundQuery:=False

Tom

"StumpedAgain" wrote:

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(Arra y( _

"ODBC;DSN=OSM;UID=OKJ/OKJ;;DBQ=CPIDOSMP;DBA=W;APA=T;EXC=F;FEN=T;QTO=T;FR C=10;FDL=10;LOB=T;RST=T;BTD=F;BNF=F;BAM=IfAllSucce ssful;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
--
-SA