By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
445,804 Members | 1,681 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 445,804 IT Pros & Developers. It's quick & easy.

Upsizing question

P: n/a
In order to upsize my Access 2003 mdb to a full SQL Server backend, I've heard
that I will need to re-write queries that take parameters from Forms and
SubForms. But re-write how?

SELECT Sum(tblTxJournal.Amount) AS Yr
FROM tblTxJournal
WHERE (Year([TxDate])=Forms!frm3!cbx3Year) And
([TxType_ID]=Forms!frm3!cbx3TxType)
GROUP BY TxType_ID;

Can I rewrite this as follows:

SELECT Sum(tblTxJournal.Amount) AS Yr
FROM tblTxJournal
WHERE (Year([TxDate])=QryPrm("frm3", "cbx3Year") And ([TxType_ID]=QryPrm("frm3",
"cbx3TxType")
GROUP BY TxType_ID;

using this function:

Public Function QryPrm(ByVal strFrm As String, ByVal strCtl As String) As
Variant
QryPrm = Forms(strFrm).Controls(strCtl)
End Function

Is this a sufficient re-wite to work with SQL server? Are there other changes I
will need to make to Upsize?

Thanks in advance...
Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
If you use an MDB with ODBC-linked SQL Server tables, your original query
should, AFAICS, still work. If you replace the MDB with an ADP, you're
facing quite extensive changes. If you're determined to use an ADP, post a
reply here and I'll look into it a bit further, but if you're using an MDB,
there's no need.

--
Brendan Reynolds

"deko" <dj****@hotmail.com> wrote in message
news:rL******************@newssvr29.news.prodigy.c om...
In order to upsize my Access 2003 mdb to a full SQL Server backend, I've heard that I will need to re-write queries that take parameters from Forms and
SubForms. But re-write how?

SELECT Sum(tblTxJournal.Amount) AS Yr
FROM tblTxJournal
WHERE (Year([TxDate])=Forms!frm3!cbx3Year) And
([TxType_ID]=Forms!frm3!cbx3TxType)
GROUP BY TxType_ID;

Can I rewrite this as follows:

SELECT Sum(tblTxJournal.Amount) AS Yr
FROM tblTxJournal
WHERE (Year([TxDate])=QryPrm("frm3", "cbx3Year") And ([TxType_ID]=QryPrm("frm3", "cbx3TxType")
GROUP BY TxType_ID;

using this function:

Public Function QryPrm(ByVal strFrm As String, ByVal strCtl As String) As
Variant
QryPrm = Forms(strFrm).Controls(strCtl)
End Function

Is this a sufficient re-wite to work with SQL server? Are there other changes I will need to make to Upsize?

Thanks in advance...

Nov 12 '05 #2

P: n/a
> If you use an MDB with ODBC-linked SQL Server tables, your original query
should, AFAICS, still work. If you replace the MDB with an ADP, you're
facing quite extensive changes. If you're determined to use an ADP, post a
reply here and I'll look into it a bit further, but if you're using an MDB,
there's no need.


Thanks for the reply.

There appears to be a lot of issues to consider... I will need to do some
homework. This was helpful:

http://msdn.microsoft.com/library/de..._designeff.asp
Nov 12 '05 #3

P: n/a
"deko" <dj****@hotmail.com> wrote:
There appears to be a lot of issues to consider... I will need to do some
homework. This was helpful:

http://msdn.microsoft.com/library/de..._designeff.asp


Thanks, added to my page at http://www.granite.ab.ca/access/sqlserverupsizing.htm.
Which may be useful to you as well.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.