Trying to drop and then create a procedure in a module

Question

1.00/5 (1 vote)

See more:

'.Execute "DROP PROC [qCustomers]"

'.Execute "CREATE PROC [qCustomers] AS (SELECT DISTINCTROW [Customers].CustomerID, [Customers] , CompanyName, Customers.ContactFirstName, Customers.ContactLastName, Customers.BillingAddress, Customers.CBillingAddress, Customers.City, Customers.StateOrProvince, Customers.PostalCode, Customers.[Country/Region] AS CountryRegion, Customers.ContactTitle, Customers.PhoneNumber, Customers.Email, Customers.CellPhoneNumber, Customers.FaxNumber, Customers.Month, Customers.Cycle, Customers.CCompanyName, Customers.CContactFirstName, Customers.CContactLastName, Customers.CContactTitle, Customers.CCountryRegion, Customers.CFaxNumber, Customers.CPhoneNumber, Customers.CCellPhoneNumber, Customers.CEmail, Customers.CPostalCode, Customers.CStateOrProvince, Customers.CCity, Customers.CopyToBilling, Customers.Notes FROM Customers;)"

'.Execute "CREATE PROC[qCustomers] AS (SELECT DISTINCTROW [Customers].CustomerID, Customers.CompanyName, Customers.ContactFirstName, Customers.ContactLastName, Customers.BillingAddress, Customers.CBillingAddress, Customers.City, Customers.StateOrProvince, Customers.PostalCode, Customers.[Country/Region] AS CountryRegion, Customers.ContactTitle, Customers.PhoneNumber, Customers.Email, Customers.CellPhoneNumber, Customers.FaxNumber, Customers.Month, Customers.Cycle, Customers.CCompanyName, Customers.CContactFirstName, Customers.CContactLastName, Customers.CContactTitle, Customers.CCountryRegion, Customers.CFaxNumber, Customers.CPhoneNumber, Customers.CCellPhoneNumber, Customers.CEmail AS Expr1, Customers.CPostalCode, Customers.CStateOrProvince, Customers.CCity, Customers.CopyToBilling, Customers.Notes FROM Customers;)"

'.Execute "CREATE PROC [qCustomers] AS (SELECT DISTINCTROW [Customers].CustomerID, [Customers].CompanyName, [Customers].ContactFirstName, [Customers].ContactLastName, [Customers].Month, [Customers].Cycle, [Customers].BillingAddress, [Customers].CBillingAddress, [Customers].City, [Customers].StateOrProvince, [Customers].PostalCode, [Customers].[Country/Region] AS CountryRegion, [Customers].ContactTitle, [Customers].PhoneNumber, [Customers].CellPhoneNumber, [Customers].FaxNumber, [Customers].CCompanyName, [Customers].CContactFirstName, [Customers].CContactLastName, [Customers].CContactTitle, [Customers].CCountryRegion, [Customers].CFaxNumber, [Customers].CPhoneNumber, [Customers].CCellPhoneNumber, [Customers].CPostalCode, [Customers].CStateOrProvince, [Customers].CCity, [Customers].CopyToBilling, [Customers].Notes FROM Customers = [Customers].CustomerID;)"

What I have tried:

Everything above...for two days...please help
I am trying to drop a procedure in an access database and then build it up again. I have the code below but nothing is working. I know I am close but not close enough. This is for a module in an old vb 6 program.

Posted 12-Jul-16 16:31pm

Member 12632012

Add a Solution

Comments

Dave Kreskowiak 12-Jul-16 22:42pm

"Not working" is not a problem description. What's the error message that's returned?
What does happen with this code compared to what's expected?

[no name] 13-Jul-16 6:23am

Im only guessing, but are you trying something like saving a Serialization to Database for future use?

Member 12632012 13-Jul-16 6:30am

The message that's returned is that there either is no permission for this action ( when I open the customers screen) or that the procedure doesn't exist. Yet when I go to the query the fields I need are there. I can't call them through the program. The only fix I have had so far is to drop the procedure and manually create the query with all of the fields. I have looked at permissions for the actual database and they all are full ..i can add, edit etc for everything. The database has the query already, I just need to add two more fields to it and can't seem to be able to do that. Please be kind. I haven't worked with VB6 in a very long time and need this to work so I can get paid and keep a roof over my head. I am in my 60's...not a kid.

[no name] 13-Jul-16 15:40pm

Im sorry, but i have sofar only done offline Desktop Applications with VB.NET. I googled and found some stuff that may be of use conerning your question;
http://www.codeproject.com/Articles/15222/How-to-Use-Stored-Procedures-in-VB
http://stackoverflow.com/questions/14326019/how-do-i-use-a-datatype-based-sql-stored-procedure-when-using-visual-basic-6

Also consider asking on the 'VBForums.com'

Member 12632012 13-Jul-16 16:45pm

I am going to try the vb forums..thanks, it's been awhile since I even looked at vb6.

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)