ADOX, IIS, ASP, ActiveX, Select into, DLL,ODBC--connection to '{SQL Server}(local)' failed

[AlexKrat]

I use VB active X DLL to backup some MS SQL 2000 records to access database.
It works perfect if I run it in the debug mode but fails if I run it from
the ASP directly.

here is my connection string

Select * into project from [ODBC;driver={SQL
Server};server=(local);database=EDMS; Trusted_Connection=yes].project tb1
where tb1.projectid = 7

I also tried to use uid =sa etc instead of trusted connection.
Please let me know if there is a solution for this:

ODBC--connection to '{SQL Server}(local)' failed

My IIS project setup not to allow anonymous login so it is all windows
authentication and I have dbo privileges for that database in SQL Server

here is my VB code:


public Sub CreateAccessDatabase()

'ADOX declaration
Dim tbl as ADOX.Table
Dim idx as ADOX.Index
Dim pk as ADOX.Key
Dim clmn as ADOX.Column

Dim FKTables() as string
Dim FKValues() as string

' Temp staff
Dim accConn as ADODB.Connection
Dim strFile as string
Dim strCn as string
Dim db_type as Integer, fktablescnt
Dim rsSchema, rsColumns, rspkeys, rskeys

' to parse through the foreign key schema
Dim TableName as string, newtablename as string
Dim column1 as string, column2 as string
' Very temp stuff
Dim SSQL, a

' Create database name
With m_utility.ArchiveAccess
strFile = BuildAccessDbName(wholeproj, .projectid, .departmentID,
.ProjName)
End With
If Dir(strFile) <> "" then Kill strFile
' build connection string
strCn = "Provider=Microsoft.Jet.OLEDB.4.0"
strCn = strCn & ";Data Source="
strCn = strCn & strFile & ".mdb"
' create access database
set cat = new ADOX.Catalog
cat.Create strCn
'Copy data we start with project table
TableName = "project"
' create Ado connection
set accConn = cat.ActiveConnection
' Copy project table
SSQL = "Select * into " & TableName & " from [ODBC;driver={SQL
Server};server=(local);database=EDMS; Trusted_Connection=yes"
SSQL = SSQL & "]." & TableName & " tb1 where tb1.projectid = " &
m_utility.ArchiveAccess.projectid
Debug.print SSQL & vbCrLf
Debug.print accConn.ConnectionString & vbCrLf
' on error GoTo errhand:
accConn.Execute SSQL




Thank you!
Alex