I have an update query that links to a mdb table. I am not sure ho

[Guest]

I have an update query that links to a mdb table. I am not sure how to code
it as a function w/ vba. The link & the inner join are confusing me. This is
my code:

UPDATE Master INNER JOIN DateTranslateTable ON
Master.TranDate=DateTranslateTable.CalendarDate SET Master.[Month] =
DateTranslateTable!CalendarMonth, Master.FiscalMonth =
DateTranslateTable!FiscalMonth
WHERE (((Master.Month) Is Null) AND ((Master.FiscalMonth) Is Null) AND
((Master.TranDate)>=[Enter Start Date]));



This query is used to add dates to fields in master table. Is this the most
efficient way to do this? Would a pass-through query to the other access db
be better?

Any help would be appreciated.

Thanks.

patti

 

[Alex Dybenko]

Hi,
yes, in general queries are most efficient way to update data. but you
linked tables from SQL server for example - then more efficient to run
update queries there, so you have to use pass-through query in access.

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[Guest]

Thanks Alex for the help.

I am getting prompted for the reportdate with this code. Could you please
advise?

Option Compare Database
Option Explicit

Function UpdateFiscalCalendarDates()

Dim reportdate As Date
reportdate = DateAdd("d", -7, Date)
Dim strSQL As String

strSQL = "Update Master INNER JOIN DateTranslateTable ON
Master.TranDate=DateTranslateTable.CalendarDate " _
& "SET Master.[Month] = DateTranslateTable!CalendarMonth,
Master.FiscalMonth = DateTranslateTable!FiscalMonth " _
& "WHERE (((Master.Month) Is Null) AND ((Master.FiscalMonth) Is Null)
AND ((Master.TranDate)>=[reportdate]));"

DoCmd.RunSQL strSQL

End Function


Thanks.

patti

Hi,
yes, in general queries are most efficient way to update data. but you
linked tables from SQL server for example - then more efficient to run
update queries there, so you have to use pass-through query in access.

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I have an update query that links to a mdb table. I am not sure how to code
it as a function w/ vba. The link & the inner join are confusing me. This
is
my code:

UPDATE Master INNER JOIN DateTranslateTable ON
Master.TranDate=DateTranslateTable.CalendarDate SET Master.[Month] =
DateTranslateTable!CalendarMonth, Master.FiscalMonth =
DateTranslateTable!FiscalMonth
WHERE (((Master.Month) Is Null) AND ((Master.FiscalMonth) Is Null) AND
((Master.TranDate)>=[Enter Start Date]));



This query is used to add dates to fields in master table. Is this the
most
efficient way to do this? Would a pass-through query to the other access
db
be better?

Any help would be appreciated.

Thanks.

patti

 

[pietlinden]

Hi,
yes, in general queries are most efficient way to update data. but you
linked tables from SQL server for example - then more efficient to run
update queries there, so you have to use pass-through query in access.

--
Best regards,
___________
Alex Dybenko (MVP)http://alexdyb.blogspot.comhttp://www.PointLtd.com

I have an update query that links to a mdb table. I am not sure how to code
it as a function w/ vba. The link & the inner join are confusing me. This
is
my code:

UPDATE Master INNER JOIN DateTranslateTable ON
Master.TranDate=DateTranslateTable.CalendarDate SET Master.[Month] =
DateTranslateTable!CalendarMonth, Master.FiscalMonth =
DateTranslateTable!FiscalMonth
WHERE (((Master.Month) Is Null) AND ((Master.FiscalMonth) Is Null) AND
((Master.TranDate)>=[Enter Start Date]));

This query is used to add dates to fields in master table. Is this the
most
efficient way to do this? Would a pass-through query to the other access
db
be better?

Any help would be appreciated.

patti

Alex, wouldn't it be better to justrun a SQL Server stored procedure
using ADO's command object and adding parameters that way? Just
prompt the user for a date and then pass that as the sp's parameter
value?

 

[pietlinden]

duh... never mind... would help if I learned to READ. I thought it
was a SQL Server DB on the backend for some reason.... might be time
for glasses...

 

[Alex Dybenko]

Hi,
you have to "insert" reportdate into SQL:

strSQL = "Update Master INNER JOIN DateTranslateTable ON
Master.TranDate=DateTranslateTable.CalendarDate " _
& "SET Master.[Month] = DateTranslateTable!CalendarMonth,
Master.FiscalMonth = DateTranslateTable!FiscalMonth " _
& "WHERE (((Master.Month) Is Null) AND ((Master.FiscalMonth) Is Null)
AND ((Master.TranDate)>=#" & format(reportdate,"mm\/dd\/yyyy") & "#));"


--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Thanks Alex for the help.

I am getting prompted for the reportdate with this code. Could you please
advise?

Option Compare Database
Option Explicit

Function UpdateFiscalCalendarDates()

Dim reportdate As Date
reportdate = DateAdd("d", -7, Date)
Dim strSQL As String

strSQL = "Update Master INNER JOIN DateTranslateTable ON
Master.TranDate=DateTranslateTable.CalendarDate " _
& "SET Master.[Month] = DateTranslateTable!CalendarMonth,
Master.FiscalMonth = DateTranslateTable!FiscalMonth " _
& "WHERE (((Master.Month) Is Null) AND ((Master.FiscalMonth) Is Null)
AND ((Master.TranDate)>=[reportdate]));"

DoCmd.RunSQL strSQL

End Function


Thanks.

patti

Hi,
yes, in general queries are most efficient way to update data. but you
linked tables from SQL server for example - then more efficient to run
update queries there, so you have to use pass-through query in access.

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

I have an update query that links to a mdb table. I am not sure how to
code
it as a function w/ vba. The link & the inner join are confusing me.
This
is
my code:

UPDATE Master INNER JOIN DateTranslateTable ON
Master.TranDate=DateTranslateTable.CalendarDate SET Master.[Month] =
DateTranslateTable!CalendarMonth, Master.FiscalMonth =
DateTranslateTable!FiscalMonth
WHERE (((Master.Month) Is Null) AND ((Master.FiscalMonth) Is Null) AND
((Master.TranDate)>=[Enter Start Date]));



This query is used to add dates to fields in master table. Is this the
most
efficient way to do this? Would a pass-through query to the other
access
db
be better?

Any help would be appreciated.

Thanks.

patti