Updating a database query from XL
 

I am trying to set up a macro where it first asks for the
current period. Then I want it to use that information to
update my database Query and return the information for
that period only.

Below is my entire macro. 3rd to the last line is where I
need to place "CurrentPeriod" instead of "3" but it is not
excepting it. The macro runs as is, returning the
information for Period 3. I am using the Get External
Data function.

Thank you in advance for your help.

Marco

CurrentPeriod = InputBox("What is the current
period?", "Curent Period", "XX")
Range("A3").Select
With Selection.QueryTable
.Connection = _
"ODBC;DSN=MS Access
Database;DBQ=D:\Backup\db2.mdb;DefaultDir=D:\Backu p;DriverI
d=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
.CommandText = Array( _
"SELECT `1540 Marco Transactional 2`.cmpcode,
`1540 Marco Transactional 2`.doccode, `1540 Marco
Transactional 2`.docnum, `1540 Marco Transactional
2`.docdate, `1540 Marco Transactional 2`.inpdate, `154" _
, _
"0 Marco Transactional 2`.yr, `1540 Marco
Transactional 2`.period, `1540 Marco Transactional 2`.el1,
`1540 Marco Transactional 2`.el2, `1540 Marco
Transactional 2`.el3, `1540 Marco Transactional 2`.el4" _
, _
", `1540 Marco Transactional 2`.el5, `1540 Marco
Transactional 2`.valuehome, `1540 Marco Transactional
2`.descr, `1540 Marco Transactional 2`.ref1, `1540 Marco
Transactional 2`.ref2, `1540 Marco Transa" _
, _
"ctional 2`.ref3, `1540 Marco Transactional
2`.ref4, `1540 Marco Transactional 2`.ref5, `1540 Marco
Transactional 2`.ref6, `1540 Marco Transactional
2`.usrname, `1540 Marco Transactional 2`.grpcode" & Chr
(13) & "" & Chr(10) & "FR" _
, _
"OM `D:\Backup\db2`.`1540 Marco Transactional 2`
`1540 Marco Transactional 2`" & Chr(13) & "" & Chr(10)
& "WHERE (`1540 Marco Transactional 2`.period=3)" _
)
.Refresh BackgroundQuery:=False

End With

Updating a database query from XL
 

Hi Marco,

Is this what you tried?

& "WHERE (`1540 Marco Transactional 2`.period=" & CurrentPeriod & ")" _

--
Regards,

Jake Marx
MS MVP - Excel
www.longhead.com

[please keep replies in the newsgroup - email address unmonitored]


Marco Rodas wrote:
I am trying to set up a macro where it first asks for the
current period. Then I want it to use that information to
update my database Query and return the information for
that period only.

Below is my entire macro. 3rd to the last line is where I
need to place "CurrentPeriod" instead of "3" but it is not
excepting it. The macro runs as is, returning the
information for Period 3. I am using the Get External
Data function.

Thank you in advance for your help.

Marco

CurrentPeriod = InputBox("What is the current
period?", "Curent Period", "XX")
Range("A3").Select
With Selection.QueryTable
.Connection = _
"ODBC;DSN=MS Access
Database;DBQ=D:\Backup\db2.mdb;DefaultDir=D:\Backu p;DriverI
d=25;FIL=MS Access;MaxBufferSize=2048;PageTimeout=5;"
.CommandText = Array( _
"SELECT `1540 Marco Transactional 2`.cmpcode,
`1540 Marco Transactional 2`.doccode, `1540 Marco
Transactional 2`.docnum, `1540 Marco Transactional
2`.docdate, `1540 Marco Transactional 2`.inpdate, `154" _
, _
"0 Marco Transactional 2`.yr, `1540 Marco
Transactional 2`.period, `1540 Marco Transactional 2`.el1,
`1540 Marco Transactional 2`.el2, `1540 Marco
Transactional 2`.el3, `1540 Marco Transactional 2`.el4" _
, _
", `1540 Marco Transactional 2`.el5, `1540 Marco
Transactional 2`.valuehome, `1540 Marco Transactional
2`.descr, `1540 Marco Transactional 2`.ref1, `1540 Marco
Transactional 2`.ref2, `1540 Marco Transa" _
, _
"ctional 2`.ref3, `1540 Marco Transactional
2`.ref4, `1540 Marco Transactional 2`.ref5, `1540 Marco
Transactional 2`.ref6, `1540 Marco Transactional
2`.usrname, `1540 Marco Transactional 2`.grpcode" & Chr
(13) & "" & Chr(10) & "FR" _
, _
"OM `D:\Backup\db2`.`1540 Marco Transactional 2`
`1540 Marco Transactional 2`" & Chr(13) & "" & Chr(10)
& "WHERE (`1540 Marco Transactional 2`.period=3)" _
)
.Refresh BackgroundQuery:=False

End With