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

P

patti

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
 
P

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

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:
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?
 
P

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

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

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

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

Similar Threads

sql query help 3
Runtime 3061: Too Few Parameters on nested query 1
query links to SQL server and mdb table 1
Update table using data in a different .mdb 0
delete records in one table that occur in another table 0
Help: I have difficulties in Update Where Exists 2
Update a table from a query 7
IIf Statement In an Update Query 4

Top