"Expected procedure not variable" error when trying to call a procedure from a database lo

[jbradleyharris]

We receive a number of similar but slightly different files from our vendors that need to be massaged into a common format for transmission to yet another vendor for processing. My predecessor wrote a new VB6 program each time a new input file format was encountered, with the expected maintenance nightmare as a result. I have been asked to combine all these programs. I thought it would be simple since they all stem from the same root with all of the actual processing done within a single module. These modules are all very similar with the differences being mainly to handle differences in the order of the columns in the input files and the account numbers needed in the output. I was able to condense the various processing modules down into just three. My plan was to build a drop-down from which to select the vendor the input file is from. This triggers a database lookup where the account parameters and the name of the correct processing module are read into variables. I then want to call the processing module and pass it the account number variables. My problem is that this returns an error "expected procedure not variable". I typed my variable as Procedure but this did not work. Is there any way I can do what I want? It seemed such a good idea at the time.

 

[strongm]

You might want to have a look at VB's CallByName function.

 

[gmmastros]

On average, what is the same of the files you are importing? Also, what database engine are you using?


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[tedsmith]

Maybe you have inadvertently used the same name for a variable and a subroutine?
Eg Sub GetFirstName(Index as Integer)
then and later on used Dim GetFirstName as String: GetFirstName = "Smith"

Or else used a VB reserved word for a Variable name like Function = 200 ?

 

[tedsmith]

You could also get it to automatically do it by finding what was unique to each type then trying a write that would cause errors then use the errors to determine which type it was.
You can read myRec!CustomerName irrespective of the order if the fields.
If the input field names are spelt differently or don't exist, trying to read each type possibility with each of the known possibilities will soon tell you which is the correct type, giving you a trappable error on the unmatched ones.

 

[strongm]

What the OP is asking is to be able to call a procedure using a name contained in a variable. Not quite sure what question the rest of you are answering ...

 

[gmmastros]

I understood the question.

In my experience, it is usually many times faster (and in my opinion, easier) to handle this sort of thing within the database. Basically, import to a generic table and then parse in to individual table(s) based on the data you find.

For example, suppose you are trying to import "people". You have Id, Name, ShoeSize and EyeColor. Someone gives you a file with the data in that order, but someone else gives you a file with id, name, EyeColor, ShoeSize (the last two columns are transposed).

You could create a table with 4 string columns, import the data, do data type validation, and then copy the data to the real table.

I asked about the size of the file because at 100's of megabytes the database option starts to become slower because of the dynamic sql executions. I also asked about the database engine, because if it was Microsoft SQL Server, I could probably help a lot.

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[strongm]

All that may be true, George, but that's not an answer to the question being asked, and your response gave no indication that you had decided to answer a completely different question, which would boil down to 'what is the best way for me to massage my data into a common format?'.

 

[tedsmith]

How about usiong the name from the dropdown list to directly select the appropriate procedure for each database type
Eg
Sub Dropdownlist_Change() or whatever

Select Case DropDownValue
Case "Blogs&Co"
Subroutine1

Case "SmithIndustries"
Subroutine2

Case "OurDatabase"
Subroutine3

End Select

End sub

If you can derive a different number interrogating each database (using error trapping as I previously suggested) you could use the number produced instead of Dropdownvalue text to avoid the dropdown list. (Case 1, Case 2, Case 3 etc)

To enable future different database formats to be added without future modifications to software being necessary, you could save the 'formats' on a separate text file and create the text Criteria for your queries from that text file.
Then instead of different procedures, use the same procedure for all (only select or change the query criteria text file instead).