N
nick.terry
Hi all,
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.
Please help, I've already spent over 8 hours trying to troubleshoot
this problem and have gotten nowhere!
Thanks,
Nick
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.
Please help, I've already spent over 8 hours trying to troubleshoot
this problem and have gotten nowhere!
Thanks,
Nick