在ACCESS中执行如下语句失败:
IF NOT EXISTS(SELECT 1 FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 and MSysObjects.Name='表名')上面的语句如果去掉IF NOT EXISTS可以查出是否存在该表
提示的error如下:
Invalid SQL statement;expected 'DELETE','INSERT',
'PROCEDURE','SELECT',or 'UPDATE'
IF NOT EXISTS(SELECT 1 FROM MsysObjects WHERE (Left([Name],1)<>"~") AND (Left$([Name],4) <> "Msys") AND (MSysObjects.Type)=1 and MSysObjects.Name='表名')上面的语句如果去掉IF NOT EXISTS可以查出是否存在该表
提示的error如下:
Invalid SQL statement;expected 'DELETE','INSERT',
'PROCEDURE','SELECT',or 'UPDATE'
STDMETHODIMP CADOTier::get_IsExistTable(BSTR bsTable, long lType, VARIANT_BOOL *pVal)
{
ADODB::_RecordsetPtr pRstSchema = NULL;
pRstSchema = m_connection->OpenSchema(ADODB::adSchemaTables);
_bstr_t bsTableName(bsTable);
_bstr_t table_name("");
_bstr_t table_type("");
char *pTemp1=NULL,*pTemp2=NULL;
pTemp1 = _com_util::ConvertBSTRToString(bsTableName);
pTemp1 = strlwr(pTemp1);
VARIANT_BOOL b=FALSE;
while(!(pRstSchema->adoEOF))
{
table_name = pRstSchema->Fields->
GetItem("TABLE_NAME")->Value;
pTemp2 = _com_util::ConvertBSTRToString(table_name);
pTemp2 = strlwr(pTemp2);
table_type = pRstSchema->Fields->
GetItem("TABLE_TYPE")->Value;
if (lType == 1) //view type
{
if (table_type == _bstr_t("VIEW"))
{
if (strcmp(pTemp1,pTemp2)==0)
b = TRUE;
}
}
if (lType == 0) //table type
{
if (table_type == _bstr_t("TABLE"))
{
if (strcmp(pTemp1,pTemp2)==0)
b = TRUE;
}
}
pRstSchema->MoveNext();
}
// Clean up objects before exit.
if (pRstSchema)
if (pRstSchema->State == ADODB::adStateOpen)
pRstSchema->Close();
*pVal = b;
return S_OK;
}
如果成功则存在
不成功则创建不过tabby的做法是最标准的