Access, SQL and VB

[EscapeUK]

I have a VB app (uses MTS) which saves data to a SQL DB with no problems at all. However when I run a long MS access Query against the DB and try to save using my VB app the VB app bombs out.

Seems to be a problem in running the MS Access (uses SQL ODBC connetion) and the VB app at the same time. I guess people must do this all the time.

Any ideas

 

[kathryn]

Could you please give a bit more detail?

Does the Access Query complete? What do you mean "the VB app bombs out"? What error message do you get?

Also, you have posted this in at least three Access forums that I saw. It is usually best to stick to one forum, at most two. I have answered you in this, since it seems the most relevant. Have you posted this to any of the VB forums?

Anyway, with a bit more detail, I'm sure that we can fix this.

Kathryn

 

[EscapeUK]

Yes the access query does complete. However our VB app returns and error message saying it is unable to save the record being entered.

Our app works fine with no error message. However when we run access we get the problem. The access queries do take along time to run.

 

[kathryn]

How are you returning the query results to VB? Are you using a recordset? Can you step through the VB code? If so, can you put a breakpoint immediately AFTER the Access Query runs and then test the recordset (if you are using one?)

Kathryn

 

[EscapeUK]

Sorry I was not clear.

I am running Access completley seperatly from the VB app. All I have done is created a simple Access form that allows the user to select some parameters and run some reports. This works fine on own and the VB app works fine on its own. However some times when a user runs the access reports the people using the VB app can not save.

P.S. In access I use and ODBC connection

 

[kathryn]

The more I understand this, the more I think that this may be a SQL issue. If I do understand you, the only thing that the Access Database and VB app have in common is the SQL database.

When users of Access are querying the SQL database, then that sometimes interferes with the VB users use of the SQL database.

Have you tried posting this to the SQL Server forum and seeing if this is a Record Locking issue with SQL?

Kathryn

 

[EscapeUK]

Yes i have but not had much of a responce. Are there any ways I can stramline my Access part. At the moment I am using ODBC and linked tables. My reports are built using the standard query approach. I also use the relationships part

 

[MichaelRed]

Hmmmmmmmmmmmmmm,

This one - again? still?

If some users are "just" in Ms. Access (as a 'play ground?) are they using the access security module? Have you checked their system set up - making SURE that they are set for "optimistic" record locking?

Ms. has stated that ODBC is the SLOOOOOOOOoooooooooest of all possible connections. You could look for other options. I do not use SQL DB (yet!) with an Ms. Access front end, so I am not familiar with the options - but surely ther is something BETTER than ODBC!



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[kathryn]

Michael's comments raised a thought. Can you get your SQL Database Administrator to move your query to SQL as a stored procedure and then call it using a pass-through query in Access? That would definitely speed things up.

Kathryn

 

[EscapeUK]

Micheal I have changed it to "No Locks" would this make any difference if the users are just viewing data as that is all they are doing in Access

Kathryn I would have to do all of that myself. Think I would be OK setting up the stored procedures. So how do I call them and change my reports etc....

 

[kathryn]

OK, there are some differences between MS Access SQL syntax and the SQL Server syntax, I believe, but nothing too major.

In SQL Enterprise Manager, or whatever you use to get to SQL, try running your query using the SQL from Access. Play around with it if you get syntax errors

When the query runs, create a new stored procedure.

In Control Panel, create a Data Source to your SQL Server.

In MS Access, create a new query, but don't add any tables. From the Query menu, choose SQL Specific -> Pass Through. You will see a blank page. From the View menu, choose Properties. Click in ODBC Connect Str box and click on the three periods at the end of the box. Navigate to your datasource and select it. Close the properties box.

Now type the EXACT name of your stored procedure on the blank page. Name your query. Open the query to run it.

Good luck. Let us know how it goes.

Kathryn

 

[EscapeUK]

Think that is going to be a nightmare to do. I just opened up on of my quiries looked at the SQL and this is what I got..

SELECT qryDetSLCMain.Office, qryDetSLCMain.dbo_CTS_Resources.Description, qryDetSLCMain.dbo_CTS_Services.Description, qryDetSLCMain.dbo_CTS_Sections.Description, qryDetSLCMain.dbo_CTS_Subjects.Description, qryDetSLCMain.CallStatus, qryDetSLCMain.CompletionDate, qryDetSLCMain.CallType, qryDetSLCMain.TITLE, qryDetSLCMain.SURNAME, qryDetSLCMain.FORENAME, qryDetSLCMain.EnquiryNo, qryDetSLCMain.DateTimeCall, qryDetSLCMain.EnquiryDate, qryDetSLCMain.TargetDate
FROM qryDetSLCMain
GROUP BY qryDetSLCMain.Office, qryDetSLCMain.dbo_CTS_Resources.Description, qryDetSLCMain.dbo_CTS_Services.Description, qryDetSLCMain.dbo_CTS_Sections.Description, qryDetSLCMain.dbo_CTS_Subjects.Description, qryDetSLCMain.CallStatus, qryDetSLCMain.CompletionDate, qryDetSLCMain.CallType, qryDetSLCMain.TITLE, qryDetSLCMain.SURNAME, qryDetSLCMain.FORENAME, qryDetSLCMain.EnquiryNo, qryDetSLCMain.DateTimeCall, qryDetSLCMain.EnquiryDate, qryDetSLCMain.TargetDate;

how on earth can I adapt that and place it in SQL

 

[kathryn]

Well, that query seems to be based on another query: qryDetSLCMain.

Can you adapt qryDetSLCMain and move it to SQL?

Kathryn

 

[MichaelRed]

O.K.,

As I (currently) understand this situation, you have a VB App which does what you want it to do - and that is manipulate recordsets in SQL Server. Your VB app uses straight SQL language to manipulate the SQL Server recordsets.

There is also some Ms. Access 'App' which others use as a 'playground' to view the data in the SQL Server db.

It (the Ms. Access 'App') has 'nothing' to do with the basic VB app, however when Ms. Acceessers are using hte Ms. Access App to generate reports the Ms. Access App. BOMBS.

Have I gotten this 'right' so far? Please provide feedback/corrections.

Are you setting security flags/parameters in eithter VB or Ms. Access query statements? Can you get the specific error being returned? Is it a "Record Locked" Err, "Access Denied" Error? Other?

Kathryn is 'suggesting' that you translate the Ms. Access queries into SQL Server Stored procedures. You, correctly, note that this can get to be quite tedious (" ... a nightmare ... &quot - and cite the example showing that you are dealing w/ nested queries, so you need to untangle the layers to do the translation.

Before 'jumping into the deep end' on this, you should consider the overall process requirements. Why are there 'users' on Ms. Access. Are the queries in Ms. Access the same as queries you are running from VB? If so, you may be creating a mainteneace issue for the future.

If you move (some of) the queries from Ms. Access to SQL Server, then - if these queries are to be used by the Ms. Accessers, they will not be able to see 'what' their getting - except in the results sense. Some - who want to critique/review the process will not be satisfied.

Could you generate the reports for the Ms. Accessers from within VB - and let them just use the VB App.?

Sorry for the rambling thought process, I just got started and kept on lurching around in the subject.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[kathryn]

Michael,

Excellent points! I

Kathryn

 

[EscapeUK]

Micheal

Thanks great points, one or two bits were a bit out. It is the vb code that crashes. The error message is one generated by the app. All the users need to be able to do is enter parameters from a form and view a report. Would placing the the quiries in SQL improve things a lot. In another forum you mentioned that using ODBC was one of the slowest methods. Do have any tips on something that could be a lot quicker apart from using my quiries as stored procedures.

 

[MichaelRed]

Certainly, placing the data access functions in SQL Server / stored procedures SHOULD dramatically improve the response for the data intensive portions of your app.


The literature I have recommends VBSQL specifically for VB to SQL Server database manipulation. However this is about two years old, so there may be something new (& IMPROVED) by now.

More importantly, I don't see the access speed as being the issue at the moment. The issue is the need to resolve the conflict between the seperate user groups (VBers ans Ms. Accessers). On this point, I do not have any new thoughts.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[EscapeUK]

I think that because Access is taking along time to get the results back it maybe causing me the problem

 

[EscapeUK]

Kathryn

I as Micheal noted I have several nested quiries. How would I go about running the stored procedure for one of them and using the results to run another.

Thanks