Re: Invalid Procedure Call

<nick.terry wrote :
Ok, this is a bit hard to classify, but it uses SQL and references
queries, so here goes...

I completed a database last thursday, and went to back it up and make
several copies. Unfortunatly in my rush to backup, I backed up the
wrong file and deleted the finished one. Luckily, I was able to
restore from a backup the night before, and only lost 1 days work.

Unfortunatly, now one of my modules will not work, giving me a generic
"Invalid Procedure Call" error. After extensive testing and
debugging, I have isolated the problem.

My module is designed to import a HUGE amount of data over ODBC from a
linked table, copy it to a local table for quick access, then format
the raw data (which involves the use of Mid and InStr) to insert into
a working table.

This is done with about 9 SQL strings total, including error
handling. My problem is occuring in the SQL string that takes the raw
data from the local table, formats it, and appends it to the final
table.

Raw Data Table: pipointlocal
Fields: tag, pointid, date

Finished Table: TagTable
Fields: PointTag, Station, TagName, PointID

SQL:

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT [pipointlocal].[tag], Mid([pipointlocal].[tag],4,InStr(4,
[pipointlocal].[tag],'_')-4) AS Station, Mid([pipointlocal].
[tag],InStr(4,[pipointlocal].[tag],'_')+1) AS TagName, [pipointlocal].
[PointID]
FROM pipointlocal;

'tag is a concatenated string which contains a station and a
'tag name, so the left and mid funtions slice it up into its
'respective parts

This SQL statement is put into a string and executed in VBA.

If I use the SQL in a query, it works fine. It only creates a problem
when executed using VBA.

<snip>

Hi Nick,

You don't mention Access nor Windows version,
but a WAG might involve the difference of "compare"
between QBE and VBA for your InStr function
(for which you have chosen to assume "default").

Again as a real WAG, I might first try adding text compare (=1)
because it "works" in QBE but not in VBA

INSERT INTO TagTable ( PointTag, Station, TagName , PointID )
SELECT
P.[tag],
Mid(P.[tag],4,InStr(4,P.[tag],'_',1)-4) AS Station,
Mid(P.[tag],InStr(4,P.[tag],'_',1)+1) AS TagName,
P.[PointID]
FROM
pipointlocal AS P;

Above assumes that SQL has been properly
formed in VBA string (no " within quotes,etc)
which I usually test by adding

Debug.Print strSQL

after assembling strSQL in VBA.

And I assume you have removed 2 parsing
lines to verify they are the problem (maybe
remove one then the other to limit if can).

Next place I might look is what values "tag"
might have (and question assumptions I am
making about "tag").

SELECT DISTINCT
P.[tag],
Len(P.[tag]) As TagLen,
InStr(1, P.[tag],'_',1) As FirstPos
FROM
pipointlocal AS P;

Once I fully understood the variants
of "tag" values, then I might look to
see where values outside my previous
assumptions would cause me to change
my parsing.

I really don't understand your parsing
for "Station" and "TagName."

It looks like you already
assume the "_" will be at pos 4?

Or will always occur on or after pos 4?

Can there be more than one "_" in string?

Or possibly a "tag" with no "_" char?

So following won't work?

Left(P.[tag], InStr(P.[tag],'_') -1) As Station,
Mid(P.[tag],InStr(P.[tag],'_')+1) AS TagName,

or

Left(P.[tag], InStr(1,P.[tag],'_',1) -1) As Station,
Mid(P.[tag],InStr(1,P.[tag],'_',1)+1) AS TagName,


in Immediate Window...

tag=null
?Mid(tag,4,InStr(4,tag,'_',1)-4) <--compile error
?Mid(tag,4,InStr(4,tag,'_',1)+1) <--compile error

What happens if you use an IIF test before your parsing?
(or in WHERE clause to weed out "bad tags")

Of course a problem with References can cause
your error message, but you said it "works"
as a query alone.

If above WAGS do not help, please provide
a list of all the various types of values "tag" can
be.

Good Luck,

gary








.

Relevant Pages

  • Re: Update existing values incrementally w/UPDATE SQL
    ... and in the book; Access 2007 VBA Programmer's Reference. ... As far as quotes go, this was one of the trickiest ... Consider vba's interpretation of a string: ... when an SQL is processed (I use an SQL example because it is the most ...
    (microsoft.public.access.modulesdaovba)
  • Re: Update existing values incrementally w/UPDATE SQL
    ... pretend that Me.txtString is a control on your form, ... You cant put Me.txtString inside the double quotes, or VBA just reads it as a ... and in the book; Access 2007 VBA Programmer's Reference. ... when an SQL is processed (I use an SQL example because it is the most ...
    (microsoft.public.access.modulesdaovba)
  • Re: "You canceled the previous operation."
    ... You can build the SQL string in VBA and then assign the string (if it is not TOO ... named query as the record source and edit its SQL. ...
    (microsoft.public.access.queries)
  • Re: SQL string in VBA
    ... die mir einen SQL String baut. ... > Anführungszeichen falsch, sodass er versucht die ... damit VBA nicht das ende des Literals interpretiert. ...
    (microsoft.public.de.access)
  • Re: Getting Start/End time, etc. from IPM.Schedule.Meeting.Resp.Po
    ... So, if I understand you correctly, I can get at these named properties by ... both marked with the same string. ... to GetIDsFromNames and "or" the returned tag with teh appropriate type (e.g. ... then iterating over the list and calling ...
    (microsoft.public.win32.programmer.messaging)