G
George
Good Afternoon,
This is my first post and I hope that I came to the right place. I have
scoured the web for answers, but it seems to no avail.
I have an Access database set up to do simple select query to pull
information from various sources using the Choose function - basically the
sources the data is pulled from depends on an index number assigned to every
record based on various logical statements that examine certain input fields
associated with each record member. That part aside, the query works exactly
as I need it to and if I run it from Access and simply copy the data over to
Excel, no problem.
Automation is the name of the game however, and I would like to import the
query via the press of a button from the Excel file, eliminating the need to
open the .mdb at all. Here is where I run into problems. This is the SQL code
of the query being used:
SELECT DISTINCT [O:\Fomi\Out\Paymin].CUSIP,
Choose(qBBRaw!Index,Null,qBBGold!NXT_REFIX_DT,qBBGold!NXT_REFIX_DT,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBRaw!MTG_NXT_PAY_DT_SET_DT,qBBRaw!MTG_NXT_PAY_DT_SET_DT,qBBRaw!MTG_NXT_PAY_DT_SET_DT,qBBGold!PREV_CPN_DT)
AS [BB Eff Mat Date],
Choose(qBBRaw!Index,Null,qBBGold!LAST_REFIX_DT,qBBGold!LAST_REFIX_DT,qBBGold!PREV_CPN_DT,qBBGold!PREV_CPN_DT,qBBGold!PREV_CPN_DT,qBBRaw!MTG_ACC_RT_START_DT,qBBRaw!MTG_ACC_RT_START_DT,qBBRaw!MTG_ACC_RT_START_DT,qBBRaw!MTG_FACT_SET_DT_STRT_NEXT_ACC_DT) AS [BB Ex Date],
Choose(qBBRaw!Index,Null,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBRaw!MTG_NXT_PAY_DT_SET_DT,qBBRaw!MTG_FACT_SET_DT_STRT_NEXT_ACC_DT,qBBRaw!MTG_FACT_SET_DT_STRT_NEXT_ACC_DT,qBBRaw!MTG_START_ACC_DT) AS [BB Pay Date],
Choose(qBBRaw!Index,Null,qBBRaw!CPN,qBBGold!CUR_CPN,qBBRaw!CPN,qBBRaw!CPN,qBBRaw!CPN,qBBRaw!CPN,qBBRaw!CPN,qBBRaw!CPN,qBBRaw!CPN) AS [BB CPN]
FROM (qBBGold INNER JOIN [O:\Fomi\Out\Paymin] ON qBBGold.CUSIP =
[O:\Fomi\Out\Paymin].CUSIP) INNER JOIN qBBRaw ON [O:\Fomi\Out\Paymin].CUSIP =
qBBRaw.CUSIP;
I've tried two methods for the VBA import coding.
One is storing the above in a string variable (QueryString) and just running:
With
..QueryTables.Add(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" _
, "Password="""";User ID=Admin;" _
, "Data Source=S:\BBShare\Corp Floater Update\Corp Floater.mdb;" _
, "**various other options**, Destination:=ActiveSheet.Cells(1, 1))
.CommandType = xlCmdSql
.CommandText = QueryString
End With
I've also tried running it directly from the Access query without
respecifying the code as such:
.CommandType = xlCmdTable
.CommandText = Array("*Query Name*")
Both have the same problem with the outcome.
That problem is as such. If you look at the SQL query string, I have 4
fields using the Choose function. No matter what I do, the first 3 fields do
NOT import, but the last one DOES. As you may notice, the first three contain
date data, where as the last one is a number field. These formats are NOT
specified in the select query explicitly. They ARE specified explicitly in
the source data linked table specs that are linked to the Access Database.
Any help would be greatly appreciated. If something is unclear or there is
some other information you guys need, please let me know and I'll try to post
as much as possible.
Running Office 2003 SP3.
This is my first post and I hope that I came to the right place. I have
scoured the web for answers, but it seems to no avail.
I have an Access database set up to do simple select query to pull
information from various sources using the Choose function - basically the
sources the data is pulled from depends on an index number assigned to every
record based on various logical statements that examine certain input fields
associated with each record member. That part aside, the query works exactly
as I need it to and if I run it from Access and simply copy the data over to
Excel, no problem.
Automation is the name of the game however, and I would like to import the
query via the press of a button from the Excel file, eliminating the need to
open the .mdb at all. Here is where I run into problems. This is the SQL code
of the query being used:
SELECT DISTINCT [O:\Fomi\Out\Paymin].CUSIP,
Choose(qBBRaw!Index,Null,qBBGold!NXT_REFIX_DT,qBBGold!NXT_REFIX_DT,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBRaw!MTG_NXT_PAY_DT_SET_DT,qBBRaw!MTG_NXT_PAY_DT_SET_DT,qBBRaw!MTG_NXT_PAY_DT_SET_DT,qBBGold!PREV_CPN_DT)
AS [BB Eff Mat Date],
Choose(qBBRaw!Index,Null,qBBGold!LAST_REFIX_DT,qBBGold!LAST_REFIX_DT,qBBGold!PREV_CPN_DT,qBBGold!PREV_CPN_DT,qBBGold!PREV_CPN_DT,qBBRaw!MTG_ACC_RT_START_DT,qBBRaw!MTG_ACC_RT_START_DT,qBBRaw!MTG_ACC_RT_START_DT,qBBRaw!MTG_FACT_SET_DT_STRT_NEXT_ACC_DT) AS [BB Ex Date],
Choose(qBBRaw!Index,Null,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBGold!NXT_CPN_DT,qBBRaw!MTG_NXT_PAY_DT_SET_DT,qBBRaw!MTG_FACT_SET_DT_STRT_NEXT_ACC_DT,qBBRaw!MTG_FACT_SET_DT_STRT_NEXT_ACC_DT,qBBRaw!MTG_START_ACC_DT) AS [BB Pay Date],
Choose(qBBRaw!Index,Null,qBBRaw!CPN,qBBGold!CUR_CPN,qBBRaw!CPN,qBBRaw!CPN,qBBRaw!CPN,qBBRaw!CPN,qBBRaw!CPN,qBBRaw!CPN,qBBRaw!CPN) AS [BB CPN]
FROM (qBBGold INNER JOIN [O:\Fomi\Out\Paymin] ON qBBGold.CUSIP =
[O:\Fomi\Out\Paymin].CUSIP) INNER JOIN qBBRaw ON [O:\Fomi\Out\Paymin].CUSIP =
qBBRaw.CUSIP;
I've tried two methods for the VBA import coding.
One is storing the above in a string variable (QueryString) and just running:
With
..QueryTables.Add(Connection:=Array("OLEDB;Provider=Microsoft.Jet.OLEDB.4.0;" _
, "Password="""";User ID=Admin;" _
, "Data Source=S:\BBShare\Corp Floater Update\Corp Floater.mdb;" _
, "**various other options**, Destination:=ActiveSheet.Cells(1, 1))
.CommandType = xlCmdSql
.CommandText = QueryString
End With
I've also tried running it directly from the Access query without
respecifying the code as such:
.CommandType = xlCmdTable
.CommandText = Array("*Query Name*")
Both have the same problem with the outcome.
That problem is as such. If you look at the SQL query string, I have 4
fields using the Choose function. No matter what I do, the first 3 fields do
NOT import, but the last one DOES. As you may notice, the first three contain
date data, where as the last one is a number field. These formats are NOT
specified in the select query explicitly. They ARE specified explicitly in
the source data linked table specs that are linked to the Access Database.
Any help would be greatly appreciated. If something is unclear or there is
some other information you guys need, please let me know and I'll try to post
as much as possible.
Running Office 2003 SP3.