VBA Error is gone After did "Compact and Repair Database"

[Sam Yi]

All,

Can any one help me explain the following situation:
When I try to set the RecordSource to current Form
Me.RecordSource = "select * from filestodownload where clientname in " & _
"(select distinct clientname from v_CapStockFundInformation where
SSCFundNumber in (" & vFundAccess & ") and live=1)"
It will raise error with description: the record source "...." specified on
this form or report doesnt exist.

And after I 'compact and repair database', the error is gone.

The database is SQL Server, and use ODBC connection

Thanks for any comment!

 

[Sam Yi]

All,

Can any one help me explain the following situation:
When I try to set the RecordSource to current Form
Me.RecordSource = "select * from filestodownload where clientname in " & _
"(select distinct clientname from v_CapStockFundInformation where
SSCFundNumber in (" & vFundAccess & ") and live=1)"
It will raise error with description: the record source "...." specified on
this form or report doesnt exist.

And after I 'compact and repair database', the error is gone.

The database is SQL Server, and use ODBC connection

Thanks for any comment!

Later I digged more deep and found the root error is raise at following
comparing point
If tepName = strTableName Then
Exit Function
End If
tepName and strTableName are both String type, and the err description is
'invalid procedure call or argument'

Any idea for this strange thing?

THanks a ton!

 

[Tom van Stiphout]

On Wed, 19 Aug 2009 02:39:01 -0700, Sam Yi

Do you have "Option Explicit" at the top of this module? If not it may
be a simple typo. Add it and compile the application.

-Tom.
Microsoft Access MVP

 

[Sam Yi]

And invalid procedure call or argument error will go after I did "compact and
repair databasse"

The problem still there, any suggestion?

 

[Sam Yi]

As you suggested, I have compiled and fixed all compile error, but the
problem still there. And it seems have sth to do with the access version env.
For instance:
DEV_1: has access 2002 and 2003 installed
DEV_2: only have access 2003 installed
user's machine: has 2003 and 2000 installed
MDB source is developed base on 2000 version

User cant open the mdb application compacted by DEV_1 with err 'invalid
procedure call or argument' on code "If tepName = strTableName Then"
And the mdb compacted by DEV_2 can be open by user.

I am close to crazy by Access's version caused strange thing.

Thanks a lot!

No, not the compact and repair. Go to your VBA editor and on the Debug -
Compile "YourDataBaseName"

And invalid procedure call or argument error will go after I did "compact and
repair databasse"

The problem still there, any suggestion?

Do you have "Option Explicit" at the top of this module? If not it may
be a simple typo. Add it and compile the application.

[quoted text clipped - 31 lines]

THanks a ton!

 

[Sam Yi]

I think it's not the duplicated name related, as I have tried to put '
"a"="a" 'at the error occurring line 'If tepName = strTableName Then ', it
still has problem.

Thank you very much!

 

[david012]

When the error goes after 'compact and repair database' then there must be database corruption issues. So, in this situation apart from 'Compact and repair', you can also go for MS SQL repair tool which works to completely resolve the sql database corruption problems and thus performs the job to repair sql database easily.