Problem importing Access Choose function into Excel via VBA - skip

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 query
SQL code 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 Excel 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 by directly refrencing the Query name without
entering the SQL code:
.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 table specs that are used to link 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.
 
R

ryguy7272

This resource should answer most of your import questions:
http://www.accessmvp.com/KDSnell/EXCEL_Import.htm

HTH,
Ryan---

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


George said:
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 query
SQL code 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 Excel 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 by directly refrencing the Query name without
entering the SQL code:
.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 table specs that are used to link 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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top