Deleting Records 6

[bdavis96]

Is there a limited to the amount of records VB can delete? I keep getting an error:

[Microsoft][ODBC Microsoft Access Driver]Query is too complex

This is my delete function:

Public Sub Delete_Records()
If Not (frmADO.PC.Recordset.EOF) Then
frmADO.PC.Recordset.MoveFirst
Do While Not (frmADO.PC.Recordset.EOF)
frmADO.PC.Recordset.Delete
frmADO.PC.Recordset.MoveNext
Loop
End If
End Sub

The error is always pointing to frmADO.PC.Recordset.Delete

Basically what I am doing with my code is setting the ADO control (PC) to the specified Query and stepping through all the records and deleting them.

Is there an easier/better way to delete all the records from a Access file?

Any help would be much appreciated.

 

[bdavis96]

I put down just the theory of Set and Close, but what are the issues with the code? I would like to see if I have everything covered in my program.

 

[MrVB50au]

bdavis96

Are you planning of others to use your app?

 

[bdavis96]

If you are asking if multiple users will be using the DB at one time, not this time, only one person. But I might be helping a friend soon on a multi-user DB, so it would be helpful to know what problems I might run into.

Also, are you familiar with Sorting dynamic arrays during Runtime?

 

[MrVB50au]

bdavis96

OK, Here's the First Issue:

Look at the path of the mdb file, you must question yourself "What if the users' mdb file is situated on another drive?" (eg: Networks have many drives for different work allocations). Then the path, what if the user had chosen a different path in that drive to put the mdb file? You need to concider this important issue and allow the user to set their own path, then have it saved so next time when the app runs, the app will find the mdb file automatically.

Second Issue:

Tho' you had cut the time down from 30 sec' to 10, to delete the table and/or records. It's still a good idea to indicate to the user some sort of activity that is still going on, Imagine if your user had thousands of records they need to delete? If there were no indication that it's currently doing it, don't you think the user would think there's something wrong and panic to the extent of a reboot?

Taking other users into concideration when creating an application is one of the utmost importance.

I had come across this in many applications I've written for companies and home based businesses. If you're planning of commercializing your app's in the future and If you don't have a group to test your applications like I do, I suggest you get a group of friends, family etc. to test the application for you and get them to write down the errors that may occur.

I am a CEO but I still have much to learn in programming even since I started way back in 1993. So, you see, not everyone is a professional. Mind you, I really think some of these programmers in this forum are tho' Man they're good!

Look at the Pro's in the MVP's List. They're what I call professionals.

 

[petermeachem]

Good points. But, we weren't asked to write the whole app, just advise on speeding one bit up.
MrVB50au, I acknowledge that most of the people in the list know a lot more than me. Doesn't preclude people not in the list from answering though. I started round about 1970, still learning.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[MrVB50au]

petermeachem

I agree, speed is very important especially in todays advanced technology and if anyone can come up with a better, faster way of doing things, I'm all for it.

bdavis96

Please excuse my pickiness, it's just that I'm too well aware of these stumbling blocks that some, NOT ALL, some programmers fall over. I just thought it was worth pointing out incase you weren't aware of the possibilities.

Andrew.

 

[bdavis96]

petermeachem, you have been programming longer then I have been around, lol, no offense. I started programming in high school in 93 with Pascal, then moved onto C++, Assembly and Visual Basic in College. As you can see, I am still a novice, but I have found Tek-Tips to be a valuable resource. I am glad to get any type of response, regardless of who it is from. It hopefully gets me on a path I wasn't understanding before.

I am glad that those tips were brought up, even though I really didn't ask about them. What I am trying to accomplish is setting one ODBC connection to an AS400 PF and the other to an access database. I clear all the access tables and repopulate the data from the AS400. There is only one user, because the access database resides on a tablet pc. The pc will be used out in the field to update information, then back at the office the information will be pushed back into the AS400 PF.

 

[bdavis96]

I said ODBC connection, I meant ADODB connection.

 

[MrVB50au]

bdavis96

Just Getting Off The Track Slightly:
I had also started my programming in 1993 with pascal and passed my programming exams with honors, or should I say with horrors. LOL . . . I liked pascal more than C, so I did some databases, writing text files, created a COBOL Compiler Interface for the good old DOS COBOL and had sold heeps of copies back then, when there was no such user friendly Interface about at all. Though I like VB, I still like going back to good old pascal every now and then to keep in touch. (You never know when someone could use a quick single exe file to do things. That's the beauty of Turbo Pascal 7.0, all you need is one exe file to run the whole application.

Fortunately, thanks to the professionalism in this forum, I was able to carry out and complete many activities that my application is to do.

I would have loved to show you what my app looks like but unfortunately this isn't possible so I guess you can't.

If there's anything else that I maybe able to help you with, let me know and hopefully, (with my limited knowledge), I could help in some way.

Good Luck!

Andrew.