access to sql 2000 newbie

[Mark Worrell]

hello to all, hope you can help.

I have an access db that is split and used by 15 users. I need to get the
backend over to sql 2000. When i try the upsizing wizard i get the message
"overflow" and connot go any further.

I also have reports based on queries that get dates from a form. How do i
reference these dates in an sql view. I have placed the sql from one of my
access queries. If i paste this in to sql 2000 i get several error messages.

SELECT TBLCUSTOMER.SURNAME, TBLCUSTOMER.FIRSTNAME, TBLTAX.TAXNUMBER,
TBLTAX.TAXYEAR, TBLTAX.DATESUBMITED, TBLTAX.ASSESSEDRESULT,
TBLCUSTOMER.CURRENT
FROM TBLCUSTOMER INNER JOIN TBLTAX ON TBLCUSTOMER.TAXNUMBER =
TBLTAX.TAXNUMBER
WHERE (((TBLTAX.DATESUBMITED) Between ([Forms]![FRMASSESSEDRESULTS]![text2])
And ([forms]![FRMASSESSEDRESULTS]![text4])) AND ((TBLTAX.ASSESSEDRESULT) Is
Null) AND ((TBLCUSTOMER.CURRENT)=False))
ORDER BY TBLTAX.DATESUBMITED;

Thanks in advance.

 

[Nathan]

Hi Mark,
Not totally sure about everything you asked, but as far
as the views go, views are sort of like permanent
queries - rather than only getting opened when you try
and access a report, or a form, they remain open at all
times. So, putting criteria into views that is temporary
(such as some info on a form) won't work. The best you
can do is create a view that draws all your data that you
might be pulling on together in one "permanent query" and
then use a regular Access query to pull the specific
records from this view.

Hope that's helpful! I haven't tried to convert an
Access DB to SQL using the upsizing wizard, so I don't
know the ins and outs of that.

Good luck,
Nathan

 

[Sylvain Lafontaine]

The overflow error is common with Access 2000 and should be corrected with
the Office 2000 Service Pack 3.

But even if it work, the Upsizing Wizard is full of bugs and will not
usually give you a working upsized database (excerpt of course for the most
simple cases). I usually use it only to upsize my tables and then edit
manually all the queries and the forms to convert them appropriately.

Using Access ADP against SQL Server have a step learning curve, especially
when you are coming from the Access - JET - DAO world.

SQL-Server doesn't see your Access Forms, only the queries; so you must
replace things like "[Forms]![FRMASSESSEDRESULTS]![text2]" with their proper
values when creating an SQL string or use appropriate parameters when
calling a stored procedure.

For you date problem, enclose them beetween simple quotes. SQL-Server
doesn't recognise the #...# syntaxe. The exact order of the month, day and
year fields inside the quotes will depend on your SQL-Server installation
and other selected options for the chosen database.

This information if for an Access ADP database. For an Access MDB database
using linked tables, the story is different. Using an MDB database is much
simpler than using an ADP but the performance will be much lower.

S. L