MS ADP project crashes everytime.

[dsdevonsomer]

Hello All,
I am in dire need to solve this issue.
System configuration Windows XP MS Access 2003. SQL Server 2000

I have started an Access Project (.adp) file with local SQL server
database with few forms. Main form has 4 tabs. First tab needs some
fields to filter records in the subform on the same tab. Out of this
fields, I have list box(4), combo box(3) and few text box. They are all
unbound, so for few of this I have "Value List" as row source type and
for others "Tables/Views/Stored Proc" as row source type.

On form load, I hide other tabs, and run queries (using Stored Proc)
against local SQL database to get recordset, which then I convert into
string using this link
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart00/html/sa00f10.asp)

It was working fine, but after adding some more combo box, list box on
second tab and adding code to populate them started all kinds of
problem.

Now, everytime I get "Microsoft Office Access has encounterd problem
and needs to close" error box. Even I am just debugging VBA code, it
crashes.

Is there anyone who can help me? I am just helpless. I have uninstalled
and re-installed access 2003. But no help.

I will seriously appreciate any help/guidance.
Regards,
Snehal

 

[RoyVidar]

Hello All,
I am in dire need to solve this issue.
System configuration Windows XP MS Access 2003. SQL Server 2000

I have started an Access Project (.adp) file with local SQL server
database with few forms. Main form has 4 tabs. First tab needs some
fields to filter records in the subform on the same tab. Out of this
fields, I have list box(4), combo box(3) and few text box. They are
all unbound, so for few of this I have "Value List" as row source
type and for others "Tables/Views/Stored Proc" as row source type.

On form load, I hide other tabs, and run queries (using Stored Proc)
against local SQL database to get recordset, which then I convert
into string using this link
(http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsmart00/html/sa00f10.asp)

It was working fine, but after adding some more combo box, list box
on second tab and adding code to populate them started all kinds of
problem.

Now, everytime I get "Microsoft Office Access has encounterd problem
and needs to close" error box. Even I am just debugging VBA code, it
crashes.

Is there anyone who can help me? I am just helpless. I have
uninstalled and re-installed access 2003. But no help.

I will seriously appreciate any help/guidance.
Regards,
Snehal

It can probably be different things, but to me the symptoms might
indicate a form corruption.

If so, then on a copy of the db, try using the /decompile option
(follow the step by step instructions here
http://www.granite.ab.ca/access/decompile.htm
if you're not familiar with it)

If that doesn't work, try the .saveastext/loadfromtext methods of
the application object on this specific form.

In the immediate pane (ctrl+g);

application.saveastext acform, "frmYourForm", "c:\frmYourForm.txt"

and load again with

application.loadfromtext acform, "frmYourForm", "c:\frmYourForm.txt"

I didn't read through the link, but in ADP 2002 and later, you can
assign ADO recordsets directly to combos, lists, and reports in
addition to forms - there shouldn't be any need to convert to strings.

Also, in a relevant event, you can feed for instance a a combo or list
with something like the below, for two parameters

me!lstMyList.rowsource = "EXEC upsMySP 12, 'test'"

 

[dsdevonsomer]

Ron,
Thanks a lot for quickest reply. I tried, saving form to text and
loading it again. But that did not help. Let me give you more
information
This form is a start up form, and so I am connecting to SQL database
using
v_strConnString = "Provider=SQLOLEDB;Application Name=MyApp;Network
Library=DBMSSOCN;Data Source=localhost;Initial Catalog=MyTestDB;user
id=TestUser;password=pwd123"
Application.CurrentProject.OpenConnection ("")
Application.CurrentProject.OpenConnection v_strConnString

Once connected, I set all combo/list boxes which has "Value List" as
row source using this.
Me.cmbRStatus.RowSource = PopulateCombo("xxxStatus", "", 0), where
PopulateCombo has stored proc call with parameters. For others with
"Tables/View/SP", I just use simple SQL as row source.

The reason to use GetString method, is to have multiple values like
"id:name:department" etc. I tried directly assigning recordset to
combobox with this, and it fails.

Most frustrating part is, on this form with 3 tabs, currently I don't
have any logic yet and it still crashes every time. I tried creating
new application from scratch and it does it again.

Many thanks for help.
Regards,
Snehal

 

[RoyVidar]

Ron,
Thanks a lot for quickest reply. I tried, saving form to text and
loading it again. But that did not help. Let me give you more
information
This form is a start up form, and so I am connecting to SQL database
using
v_strConnString = "Provider=SQLOLEDB;Application Name=MyApp;Network
Library=DBMSSOCN;Data Source=localhost;Initial Catalog=MyTestDB;user
id=TestUser;password=pwd123"
Application.CurrentProject.OpenConnection ("")
Application.CurrentProject.OpenConnection v_strConnString

Once connected, I set all combo/list boxes which has "Value List" as
row source using this.
Me.cmbRStatus.RowSource = PopulateCombo("xxxStatus", "", 0), where
PopulateCombo has stored proc call with parameters. For others with
"Tables/View/SP", I just use simple SQL as row source.

The reason to use GetString method, is to have multiple values like
"id:name:department" etc. I tried directly assigning recordset to
combobox with this, and it fails.

Most frustrating part is, on this form with 3 tabs, currently I don't
have any logic yet and it still crashes every time. I tried creating
new application from scratch and it does it again.

Many thanks for help.
Regards,
Snehal

If it is not corruption, but issues relating to ADP/SQL server,
perhaps try microsoft.public.access.adp.sqlserver which is dedicated
to ADPs. I think this is probably out of my league.

I have no experience with changing connection in ADP - do you really
need that? What happens if you skip that?

If no one else pops in with suggestions, then I'd be inclined to
suggest to try to comment all the code in this form, and try
to add (uncomment) incrementally, and see at what code it starts
crashing.

I prefer not to use Value List/Getstring when I can use Table/View/SP
and simple code as in my previous reply, which I've found to work well
also with parameterized SPs.

To relate it to/use the sample in your link:

With an Access query like this

SELECT Field1 FROM Table1
WHERE Field2 = [Forms]![frmFName]![ctlParam]

You'd create an SP like this

Create Procedure dbo.procCreateMyList
(
MyParam1 varChar(50)
)
AS
SELECT
field1
FROM
tblTable1
WHERE
field2 = @MyParam1

Then in a relevant event

Me!lstMyList.RowSource = "EXEC dbo.procCreateMyList '" & _
Me!ctlParam & "'"

....assuming this parameter is fetched from the currnet form, else
one would need full referencing through the forms collection.

 

[dsdevonsomer]

Ron,
Thank you once again. I just provided SQL piece of code as a reference,
just in case if someone knows about any SQL related issues. I read
somewhere while I was researching this issue, that every ADP opens up 3
connection to SQL (checking in profiler) . So, I thought it may help.
SQL Server is not issue, it's how it is connected to from ADP, is an
issue.

I will try your first post recommendation. I have printed out the link
and will try it out tonight.
For combo box, I will change all Value List to Table/SP/View. But for
the recordset as rowsource vs getstring, I have a two questions.

Q 1 Can I use query like this and assign recordset to Combobox ?

SELECT CAST(ID AS VARCHAR) + ''|'' + CODE, DESCRIPTION FROM DEPARTMENT
ORDER BY CODE.

In my case, combo box settings are
RowSourceType = "Table/View/Stored Proc"
Column Count 2
Bound Column 1
Column Width 0";1"

Then I use, "Code" in another query for different Combo box.

Q 2. What is the best practice to set/keep combo box Row Source? In my
code, when I am switching between tabs, should I reset RowSource to
empty or keep them? Also eventhough when I exit from the application, I
use Me!lstMyList.RowSource = "", I can still see the values in
RowSource in design mode. Why is that?

Ron, you have been a great help.
Regards,
Snehal

Ron,
Thanks a lot for quickest reply. I tried, saving form to text and
loading it again. But that did not help. Let me give you more
information
This form is a start up form, and so I am connecting to SQL database
using
v_strConnString = "Provider=SQLOLEDB;Application Name=MyApp;Network
Library=DBMSSOCN;Data Source=localhost;Initial Catalog=MyTestDB;user
id=TestUser;password=pwd123"
Application.CurrentProject.OpenConnection ("")
Application.CurrentProject.OpenConnection v_strConnString

Once connected, I set all combo/list boxes which has "Value List" as
row source using this.
Me.cmbRStatus.RowSource = PopulateCombo("xxxStatus", "", 0), where
PopulateCombo has stored proc call with parameters. For others with
"Tables/View/SP", I just use simple SQL as row source.

The reason to use GetString method, is to have multiple values like
"id:name:department" etc. I tried directly assigning recordset to
combobox with this, and it fails.

Most frustrating part is, on this form with 3 tabs, currently I don't
have any logic yet and it still crashes every time. I tried creating
new application from scratch and it does it again.

Many thanks for help.
Regards,
Snehal

If it is not corruption, but issues relating to ADP/SQL server,
perhaps try microsoft.public.access.adp.sqlserver which is dedicated
to ADPs. I think this is probably out of my league.

I have no experience with changing connection in ADP - do you really
need that? What happens if you skip that?

If no one else pops in with suggestions, then I'd be inclined to
suggest to try to comment all the code in this form, and try
to add (uncomment) incrementally, and see at what code it starts
crashing.

I prefer not to use Value List/Getstring when I can use Table/View/SP
and simple code as in my previous reply, which I've found to work well
also with parameterized SPs.

To relate it to/use the sample in your link:

With an Access query like this

SELECT Field1 FROM Table1
WHERE Field2 = [Forms]![frmFName]![ctlParam]

You'd create an SP like this

Create Procedure dbo.procCreateMyList
(
MyParam1 varChar(50)
)
AS
SELECT
field1
FROM
tblTable1
WHERE
field2 = @MyParam1

Then in a relevant event

Me!lstMyList.RowSource = "EXEC dbo.procCreateMyList '" & _
Me!ctlParam & "'"

...assuming this parameter is fetched from the currnet form, else
one would need full referencing through the forms collection.

 

[RoyVidar]

Ron,
Thank you once again. I just provided SQL piece of code as a
reference, just in case if someone knows about any SQL related
issues. I read somewhere while I was researching this issue, that
every ADP opens up 3 connection to SQL (checking in profiler) . So, I
thought it may help. SQL Server is not issue, it's how it is
connected to from ADP, is an issue.

I will try your first post recommendation. I have printed out the
link and will try it out tonight.
For combo box, I will change all Value List to Table/SP/View. But for
the recordset as rowsource vs getstring, I have a two questions.

Q 1 Can I use query like this and assign recordset to Combobox ?

SELECT CAST(ID AS VARCHAR) + ''|'' + CODE, DESCRIPTION FROM
DEPARTMENT ORDER BY CODE.

In my case, combo box settings are
RowSourceType = "Table/View/Stored Proc"
Column Count 2
Bound Column 1
Column Width 0";1"

Then I use, "Code" in another query for different Combo box.

Q 2. What is the best practice to set/keep combo box Row Source? In
my code, when I am switching between tabs, should I reset RowSource
to empty or keep them? Also eventhough when I exit from the
application, I use Me!lstMyList.RowSource = "", I can still see the
values in RowSource in design mode. Why is that?

Ron, you have been a great help.
Regards,
Snehal

This is out of my league.

It's not really a recordset as rowsource, but it's assigning a
recordset to the recordset property of the combo, list, report
or form.

If your query does work *), you should be able to use something
like the below air code.

Dim rs as adodb.recordset
set rs = new adodb.recordset
with rs
set .activeconnection = currentproject.connection
.locktype = adlockoptimistic
.cursortype = adopenforwardonly
.cursorlocation = aduseclient
.open strSql, , , , adcmdtext
set me.controls("cboYourCombo").recordset = rs
.close
end with

What is best - I don't know - but I always feel that going through
opening recordset, then convert to string, which is then assigned
to a valuelist rowsourcetype is adding unnecessary overhead.

I usually don't remove rowsource when moving between tabs or other
stuff, but depending on how heavy the form is, I might not set it
until I enter the tab - or even the gotfocus of the combo.

Usually, if you empty all the rowsources/recordsources in design,
view I think they should stay empty after usage.

But does this mean that it doesn't crash anymore?

*) I'm getting errors with similar SQL as yours, perhaps it's based
on whether or not the QUOTED_IDENTIFIER is set to ON or OFF? Or are
you intending a Bitwise OR or some other things I don't understand?

 

[dsdevonsomer]

Thanks Roy,

I understand the overhead of converting RS to string and assigning to
Value List. I have started with brand new file and adding one by one
controls and testing it. If I copy controls from old app, it still
crashes.

Inthe meantime, I will also implement your suggestion to assign
recordset directly to combo box RS. But wait, which option is better ?
1. Use SP with Parameter in RowSource
2. Using your suggested code with RS assigning.
I think both are the same.. right. If so, once executed, my app should
be in disconnected state. That's all I want.

About the list/combo box being empty after I exit. No it does not clear
that out for some reason.

One more thing I did as a change in new app file. I used AutoExec macro
to connect to SQL Server and then once that is done, I load the main
form. Hopefully, I should be able to finish reproduction of the tabbed
form by midnight.

If I am lucky not to crash my app, I will update you.

Thanks again Ron,
Snehal

 

[Sylvain Lafontaine]

The newsgroup for ADP is m.p.a.adp.sqlserver. On Google:
http://groups.google.ca/group/microsoft.public.access.adp.sqlserver


The best way of setting a combobox is to set its record source to an EXEC
statement:

Me!lstMyList.RowSource = "EXEC dbo.procCreateMyList '" & _
Me!ctlParam & "'"

Using a SP with a parameter is also good but can lead to a lot unecessary
requeries by the ADP engine if it's located on a subform and you change the
values of other bound controls on the subform or the main form. You can
take a look with the SQL-Profiler to verify if this this happens or not.

For the statement « SELECT CAST(ID AS VARCHAR) + ''|'' + CODE, DESCRIPTION
FROM DEPARTMENT ORDER BY CODE », I don't see why you are changing the value
of the linked (and hidden) field but sure, you can do this if you want; why
not?