Access 2000 Form Does Not Return Data After Upsize to Project

[David C. Barber]

upsized an MDB to ADP/SQL Server 2000 under Access 2000. All the DAO code
that I've changed to ADO code is working fine, HOWEVER the form Record
Source itself does not seem willing to return data. I've set the Record
Source to both the query, and the SQL contained within the query, and
although the system pauses long enough to have gone out and retrieved the
data, I can't see it. The form itself remains gray. In addition:

DoCmd.GoToRecord, ,acNewRecord

gives a error #2105 Unable to move to Record. (I'm trying to add a new
record here.) Yes, AllowAdditions and all other allows are set True on my
form, and permissions on SQL Server seem correct, especially since I can see
the data otherwise.

Furthermore, if I try to get the recordset so that I can manipulate it
myself in ADO with:

Dim MyRS as ADODB.Recordset
Set MyRS = me.recordset

The MyRS.recordcount is 0, and both BOF and EOF are True. (Note I've tried
doing this in both the form Open and Load events, where the data should have
already arrived. In those same events I can do the following without
problem:

Dim adoTest As New ADODB.Recordset
adoTest.Open "qryCertificates", CurrentProject.Connection, adOpenKeyset,
adLockOptimistic, adCmdStoredProc

The test dataset shows all the records present, and I can do an .AddNew with
no problem. This form has literally dozens of controls and some events tied
to the form-based recordset which is why I want to use what Access provides
here, and I'm fast running out of time on this. Of course I didn't write it
this way to start with, but it's mine to maintain now.

Can anyone offer any suggestions on why my form RecordSource seems to be
hitting my SQL database, yet not making any data available on its return?

Thanks!

*David*

[chris smith]

I had a similar problem Eventually traced it to data integrity issues
relating to Primary Key.
"David C. Barber" <da***@NOSPAMdbarber.com> wrote in message
news:2N********************@comcast.com...

upsized an MDB to ADP/SQL Server 2000 under Access 2000. All the DAO code
that I've changed to ADO code is working fine, HOWEVER the form Record
Source itself does not seem willing to return data. I've set the Record
Source to both the query, and the SQL contained within the query, and
although the system pauses long enough to have gone out and retrieved the
data, I can't see it. The form itself remains gray. In addition:

DoCmd.GoToRecord, ,acNewRecord

gives a error #2105 Unable to move to Record. (I'm trying to add a new
record here.) Yes, AllowAdditions and all other allows are set True on my
form, and permissions on SQL Server seem correct, especially since I can
see
the data otherwise.

Furthermore, if I try to get the recordset so that I can manipulate it
myself in ADO with:

Dim MyRS as ADODB.Recordset
Set MyRS = me.recordset

The MyRS.recordcount is 0, and both BOF and EOF are True. (Note I've
tried
doing this in both the form Open and Load events, where the data should
have
already arrived. In those same events I can do the following without
problem:

Dim adoTest As New ADODB.Recordset
adoTest.Open "qryCertificates", CurrentProject.Connection,
adOpenKeyset,
adLockOptimistic, adCmdStoredProc

The test dataset shows all the records present, and I can do an .AddNew
with
no problem. This form has literally dozens of controls and some events
tied
to the form-based recordset which is why I want to use what Access
provides
here, and I'm fast running out of time on this. Of course I didn't write
it
this way to start with, but it's mine to maintain now.

Can anyone offer any suggestions on why my form RecordSource seems to be
hitting my SQL database, yet not making any data available on its return?

Thanks!

*David*

[David C. Barber]

Found my problem too. In my case the Data Entry property of the Form was
set True. Even though it was also True in the MDB version, it acts a whole
lot different in an ADP. You don't get any records, hence the problem with
DoCmd.GoToRecord, and the screen stays gray, though that may partly be
another problem.

Also, some rather sloppy queries had been used in the MDB (written by
someone prior to myself). I'll tell you something folks, SQL Server doesn't
let you get away anything like you get used to doing in Access.

And the Upsize Wizard isn't smart enough even to change the Access wildcard
character "*" (used in LIKE comparisons) to the SQL Server "%" wildcard
character.

At least my screens come up now.

And although the auto completion function of Access stops at:
"me.Recordset", you can still type in "me.Recordset.Recordcount" and other
ADO functions and they seem to work just fine.

Live, and learn.

*David*

"chris smith" <ch***@bitsltd.co.uk> wrote in message
news:co**********@sparta.btinternet.com...

I had a similar problem Eventually traced it to data integrity issues
relating to Primary Key.
"David C. Barber" <da***@NOSPAMdbarber.com> wrote in message
news:2N********************@comcast.com...

upsized an MDB to ADP/SQL Server 2000 under Access 2000. All the DAO code that I've changed to ADO code is working fine, HOWEVER the form Record
Source itself does not seem willing to return data. I've set the Record
Source to both the query, and the SQL contained within the query, and
although the system pauses long enough to have gone out and retrieved the data, I can't see it. The form itself remains gray. In addition:

DoCmd.GoToRecord, ,acNewRecord

gives a error #2105 Unable to move to Record. (I'm trying to add a new
record here.) Yes, AllowAdditions and all other allows are set True on my form, and permissions on SQL Server seem correct, especially since I can
see
the data otherwise.

Furthermore, if I try to get the recordset so that I can manipulate it
myself in ADO with:

Dim MyRS as ADODB.Recordset
Set MyRS = me.recordset

The MyRS.recordcount is 0, and both BOF and EOF are True. (Note I've
tried
doing this in both the form Open and Load events, where the data should
have
already arrived. In those same events I can do the following without
problem:

Dim adoTest As New ADODB.Recordset
adoTest.Open "qryCertificates", CurrentProject.Connection,
adOpenKeyset,
adLockOptimistic, adCmdStoredProc

The test dataset shows all the records present, and I can do an .AddNew
with
no problem. This form has literally dozens of controls and some events
tied
to the form-based recordset which is why I want to use what Access
provides
here, and I'm fast running out of time on this. Of course I didn't write it
this way to start with, but it's mine to maintain now.

Can anyone offer any suggestions on why my form RecordSource seems to be
hitting my SQL database, yet not making any data available on its return?
Thanks!

*David*