Hello,

I have created a database for a directory I am creating for a new e-commerce site. I have several tables and I ran a query in access 2003. I was trying to export this to our SQL Database on our website. I am not even sure this is possible. I have no problem exporting normal tables, but when I export queries I get the following error:


ODBC – Call Failed

[Microsoft][ODBC SQL Server Driver][SQL Server]Multiple Identity Columns Specified for table ‘tbl_Category_Query’. Only one identity column per table is allowed. (#2744)

Does anyone have any idea how I can export a query in Access 2003 to MS SQL? I need this query because I am trying to use ASP to run a stored procedure, where I have combined some of the tables needed to for my directory.

I am using the following ASP Code to call this stored procedure:

Set objComm = Server.CreateObject("ADODB.Command")

objComm.ActiveConnection = strConnect
objComm.CommandText = "tbl_Category_Query1"
objComm.CommandType = adCmdStoredProc

Set objRS= objComm.Execute
Set objComm = Nothing

I get the following message when this code is run:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'
[Microsoft][ODBC SQL Server Driver][SQL Server]Could not find stored procedure 'tbl_Category_Query1'.
/Directory/TMP9l2l2vq22x.asp, line 34

I am assuming I receive this error message because I have not yet exported my query.
I appreciate your help.

With your Query open in Access design mode, click the SQL view button on the toolbar. Copy the SQL text to clipboard.

Then open your SQL server using an Access Project file. (Lookup in Access Help) Select "Views" from the navigation bar. Click New|Stored Procedure. Again click the SQL view and paste your SQL text into the SQL window. Click out of the SQL view window and watch as Access builds the stored procedure before you eyes.

Make further changes as needed such as setting any default values and formats for you fields. (i.e. replacing Now() with getdate(), etc.)

I have tried several methods, including the Access Upsizing Wizard. The method outlined above seems to work best.

Thank you for your help,

I have used this method several times and it works almost all the time. I came across a problem while using a criteria in the design wizard. I am more or less trying to run a filter for two different parameters. I am not sure how to do this with a recordset, I can only use one filter at a time: RS.Filter = "Company_ID = '50772'". So I created a stored procedure using the following statement:

  strSQL = "SELECT tbl_Comments_Complaints.Comment_ID, tbl_Comments_Complaints.Company_ID, tbl_Comments_Complaints.Company_Name, tbl_Comments_Complaints.Customer_Comments, tbl_Comments_Complaints.Customer_Rating, tbl_Comments_Complaints.Complaint, tbl_Comments_Complaints.Resolved, tbl_Comments_Complaints.Complaint_Resolution, tbl_Comments_Complaints.Complaint_Filed, tbl_Comments_Complaints.Complaint_Resolved, tbl_Comments_Complaints.Status, tbl_Comments_Complaints.Positive, tbl_Comments_Complaints.Negative, tbl_Comments_Complaints.email, tbl_Comments_Complaints.First_Name_Comp, tbl_Comments_Complaints.Last_Name_Comp, tbl_Comments_Complaints.Approved " & _
    "FROM tbl_Comments_Complaints " &_
    "WHERE (((tbl_Comments_Complaints.Approved)=True)) " & _
    "ORDER BY tbl_Comments_Complaints.Comment_ID; "

However, I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'True'.

/directory/complaint_view.asp, line 43


I am not sure why the criteria, i.e. the record must be equal to true, generates the above error. It seems to work fine in Access 2000 and 2003.

Any help would be welcomed!

Simply change the condition to
WHERE ... <YourBooleanField>=1 (in case of TRUE)

In case of FALSE use =0

Robert
www.military.cz