Invalid Procedure Call

[nick.terry]

Hi all,

I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.

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.

Please help, I've already spent over 8 hours trying to troubleshoot
this problem and have gotten nowhere!

Thanks,

Nick

 

[Scott McDaniel]

Hi all,

I am a veteran reader, but a first time poster, so please bear with me
and if you need more info or something, just let me know.

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.

Are you sure that you have values in the pipointlocal.tag field, and that the Mid and InStr functions are returning data
as anticipated? Try printing out the SELECT portion of the SQL statement in the Immediate window and see what happens
....

Please help, I've already spent over 8 hours trying to troubleshoot
this problem and have gotten nowhere!

Thanks,

Nick

Scott McDaniel
scott@takemeout_infotrakker.com
www.infotrakker.com

 

[nick.terry]

Scott,

Thank you for taking the time to reply! I'm not sure exactly what you
want me to do with this though. I tried adding

Debug.Print strSQL4

to my code and running it, but it just gave me the same thing I had
written in the code to begin with. The thing about the Mid and InStr
functions is that they modify the data in each record, while the SQL
is running, not before hand (i.e. SELECT name FROM table WHERE name =
" & Mid(txtname,,,) )

I have put this SQL statement into a query, and it runs fine,
displaying the correct data, and adding it to the table. Its only
when I use it in my module that it freaks out.

BTW I checked my pipointlocal.tag and yes it does have data.


Nick

 

[Tony Toews [MVP]]

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

Just guessing. You did mention ODBC. And wield things could be
happening there. Possibly the mid and instr are being passed to the
other server although I kind doubt it.

Can you stuff those data without using any mid, instr or other
function calls into a field(s) in the table? Then later run the mid,
instr and update the other fields.

Tony
--
Tony Toews, Microsoft Access MVP
Please respond only in the newsgroups so that others can
read the entire thread of messages.
Microsoft Access Links, Hints, Tips & Accounting Systems at
http://www.granite.ab.ca/accsmstr.htm
Tony's Microsoft Access Blog - http://msmvps.com/blogs/access/