ADOX... Unknown runtime error

[Ivan Debono]

Hi,

I keep getting an Unknown runtime error on line 3 below:

1 If oField.Type <> 136 Then 'adChapter
2 If Right(oField.name, 3) = "_id" Then
3 For Each oKey In oTable.Keys
4 If oKey.Type = 2 Then 'adKeyForeign
5 Set oCol = oKey.Columns(0)

Something wrong with this??

Thanks,
Ivan

 

[Ray Costanzo [MVP]]

What kind of database? What is the rest of the code?

Ray at home

 

[Ivan Debono]

Access 2000 format. here's the whole snippet:

'Check for foreign keys
For Each oField In oSource.fields
If oField.Type <> 136 Then 'adChapter
If Right(oField.name, 3) = "_id" Then
For Each oKey In oTable.Keys
If oKey.Type = 2 Then 'adKeyForeign
Set oCol = oKey.Columns(0)
If (oCol.name = oField.name) And (oField.Value > 0) Then
On Error Resume Next
lFID = 0
lFID = Session.Contents(oKey.RelatedTable & "." & oField.Value)
If (Err.Number <> 0) or (lFID = 0) Then
lFID = oField.Value
Err.Clear
End If
On Error GoTo 0

If lFID <> oField.Value Then
oField.Value = lFID
End If

Exit For
End If
End If
Next
End If
End If
Next

 

[Ray Costanzo [MVP]]

My guess is that the problem is not with your code. Perhaps reinstalling
MDAC would help? Are you using an OLEDB connection?

This code, run on a W2K Server, works fine. I commented out all the stuff
after line 3, the one where you got an error, and all went well.


<%
Dim oADO, oADOX, oTable
Set oADO = CreateObject("ADODB.Connection")
Set oADOX = CreateObject("ADOX.Catalog")
oADO.Open"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=D:\Usw\test.mdb;"
oADOX.ActiveConnection = oADO

For Each oTable In oADOX.Tables
'For Each oField In oSource.fields
' If oField.Type <> 136 Then 'adChapter
' If Right(oField.name, 3) = "_id" Then
For Each oKey In oTable.Keys
Response.Write oKey.Type



'If oKey.Type = 2 Then 'adKeyForeign
' Set oCol = oKey.Columns(0)
' If (oCol.name = oField.name) And (oField.Value > 0) Then
' On Error Resume Next
' lFID = 0
' lFID = Session.Contents(oKey.RelatedTable & "." &
oField.Value)
' If (Err.Number <> 0) or (lFID = 0) Then
' lFID = oField.Value
' Err.Clear
' End If
' On Error GoTo 0
' If lFID <> oField.Value Then
' oField.Value = lFID
' End If
' Exit For
' End If
'End If
Next
' End If
' End If
'Next

Next

Set oADOX = Nothing
oADO.Close : Set oADOX = Nothing

%>


Also check out the Access alternative option here.
http://www.aspfaq.com/show.asp?id=2520

Ray at work

 

[Ivan Debono]

I'm suspecting it has to do with the connection. Unfortunately I just can't
reinstall MDAC... the website is hosted at an ISP

I returned the connection string and it was really strange. The provider was
MSDASQL, a DBQ property instead of Data Source, and other ODBC properties
that I never used... I configured the DSN using the site's control panel.

The code is definitely good because the same code is used locally and works
100%. I'll be changing the connection string and hope for the best!!

Ivan

 

[Ray Costanzo [MVP]]

Ah, yes, instead of using the DSN setup through that control panel, can you
just use a "good ole" OLEDB connection string? Assuming that your Access
databases are stored in a directory, /databases, on your site:


sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("/databases/yourfile.mdb")
oADO.OPen sConnectionSTring

Ray at work

 

[Ivan Debono]

It works now!!

Ah, yes, instead of using the DSN setup through that control panel, can
you just use a "good ole" OLEDB connection string? Assuming that your
Access databases are stored in a directory, /databases, on your site:


sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" &
Server.MapPath("/databases/yourfile.mdb")
oADO.OPen sConnectionSTring

Ray at work

 

[Andre Kemper]

Ein Malteser in Deutschland?