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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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

Alex Dybenko said:
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

patti said:
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
Click to expand...
Click to expand...
 
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:
Click to expand...
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]));
Click to expand...
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?
Click to expand...
Any help would be appreciated.

patti
Click to expand...
Click to expand...

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?
 
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...
 
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

patti said:
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

Alex Dybenko said:
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

patti said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top