Access 2003, sudden code failure

[Lysander]

Below is a section of code that I have been running for over 6 years on many databases. It helps me create tables quickly. Today, with no changes, when it gets to the create property line, if falls over with Error 13, Type Mismatch. On every property line, on every database.

Any idea what is going on please.

    If Len(rs!Description & "") > 0 Then
        Set prp = fld.CreateProperty("Description", dbText, rs!Description)
        fld.Properties.Append prp
    End If
    If Len(rs!RowSource & "") > 0 Then
        Set prp = fld.CreateProperty("DisplayControl", 3, 111)
        fld.Properties.Append prp

[twinnyfo]

Lysander,

My condolences! I've had stuff like this happen to me before, too. Have you had any recent updates to MS Office? You mention Access 2003, and such weird behavior seems to fit more in a switch to 2007/2010, but not a minor version change (but which has bitten me in the past).

I am sure you have troubleshot to find out if rs!Description is, in fact, text?

Another one of the headache-inducing idiotsyncracies of MS Access.... But what can you do? It's all we got!

[Lysander]

Nope, no updates, no new software, last thing I loaded was a game, 6 months ago, and code was working after that. Code actually worked last Friday. As it is failing on all databases, not a database issue, either my references have got corrupt, or something else is screwing up my PC. This is critical time-saving code I have been using for years. Thankfully, the table I need to create is only 12 fields this time, so I can do it manualy this afternoon, but, a real pain in the proverbials.

Description, on the table, is indeed text. It says things like "This is women who have given birth" or "Total Pregnacies so far" etc.

[zmbd]

run the debug compile and option setups as descriped in the general debugging steps as outlined here:
> Before Posting (VBA or SQL) Code

I suspect that you don't have option explicit set and that there is a typo somewhere; however, without the remaining code that is only a guess (^_^)

[Lysander]

Sadly, I wish that was the answer, but, I have been forcing Option Explict since Access 2.5, when it first appeared.

I can't post the full code, I don't think, they limit to 20 lines, but I will try. Nothing has changed for 6 years. It worked on Friday, failed today.

Here is the full code, if they let me post it

Option Compare Database
Option Explicit 

Sub test()
Dim db As Database
Dim td As TableDef
Dim fld As Field
Dim prp As Property
Dim i As Integer, j As Integer, var As Variant
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT id,Caption, Name, Description, Type, Size, RowSource FROM Sheet1 ORDER BY ID;")
Set td = db.TableDefs("tblSchoolSurvey")
While Not rs.EOF
Select Case rs!Type
Case "dblong"
    Set fld = td.CreateField(rs!Name, dbLong)
Case "dbText"
    Set fld = td.CreateField(rs!Name, dbText, rs!Size)
End Select
td.Fields.Append fld
    If Len(rs!Caption & "") > 0 Then
        Set prp = fld.CreateProperty("Caption", dbText, rs!Caption)
        fld.Properties.Append prp
    End If
    If Len(rs!Description & "") > 0 Then
        Set prp = fld.CreateProperty("Description", dbText, rs!Description)
        fld.Properties.Append prp
    End If
    If Len(rs!RowSource & "") > 0 Then
        Set prp = fld.CreateProperty("DisplayControl", 3, 111)
        fld.Properties.Append prp
        Set prp = fld.CreateProperty("RowSourceType", 10, "Table/Query")
        fld.Properties.Append prp
        Set prp = fld.CreateProperty("RowSource", 12, rs!RowSource)
        fld.Properties.Append prp
        Set prp = fld.CreateProperty("BoundColumn", 3, 1)
        fld.Properties.Append prp
        Set prp = fld.CreateProperty("ColumnCount", 3, 2)
        fld.Properties.Append prp
        Set prp = fld.CreateProperty("ColumnHeads", 1, False)
        fld.Properties.Append prp
        Set prp = fld.CreateProperty("ColumnWidths", 10, "567;2268")
        fld.Properties.Append prp
        Set prp = fld.CreateProperty("ListWidth", 10, "2835twip")
        fld.Properties.Append prp
        Set prp = fld.CreateProperty("LimitToList", 1, True)
        fld.Properties.Append prp
    End If
    rs.MoveNext
Wend
rs.Close
Set rs = Nothing
Set prp = Nothing
Set fld = Nothing
Set td = Nothing
Set db = Nothing

End Sub

[twinnyfo]

And you said your cod3e is dying on Line 25 with a Type Mismatch? Try inserting

Debug.Print rst!Description

after line 24 just to see what the VB sees in that field. Totally strange that htis would work Friday, but not today........

[zmbd]

Running thru your code.
The only thing I would change is to explicitly set:

'...
Dim db As DAO.Database
Dim td As DAO.TableDef
Dim fld As DAO.Field
Dim prp As DAO.Property
'...
Dim rs As DAO.Recordset
...

The posted code you ran just fine on my ACC2010 development PC. I did however set the DAO explicitly.

So the first thing I would check would be the: Sheet1 the actual data entry in the recordset. Verfiy that something has not corrupted the recordset.

Next: Tools>Reference s... I do not expect this to be the issue.

Then I would set either DAO or ADO depending on your preference for development. (BTW: As of late, it is considered preferable to explicitly declaire DAO/ADO/etc so as to prevent library issues)


You say you make a ton of tables... the same database over and over again?

[Lysander]

twinnyfo, the rs!description says "Lookup tlkStatus". I copied the line into the immeditate window and used "FRED" and "PETER" as the text, same failure.

[Lysander]

zmbd
Thank you. Hugs and love.
Putting DAO. in front of the declarations solved the problem.

I can only imagine that a game or something else I have loaded has used Access and caused a conflict between DAO and ADO.

And yes, I have been using this code, on over a dozen databases, over and over again since 2006

All working now.

Thanks again.

[twinnyfo]

Lysander,

Great to hear that you found success!

[zmbd]

*(^_^)*
Warm Fuzzies... need that today.. the lab has Grimlins again.

Be interesting to find out what and why the game changed things in the system. (^_^) Wouldn't surprise me if there was some update to the DLLs in the commonfiles folder - or they may have added the ADO reference. ACC2003 is supposed to be DAO by default;however , some run with both ADO and DAO set and that will cause issues.

IMHO: Poor programming on the gammers part if that is what happened - I was taught that you didn't touch other program's cores nor depedencies. sigh. old school

Something else I wanted to mention:

(air code...)
Set rs = db.OpenRecordset("SELECT 
id,Caption, 
[B][u]Name[/u][/B], [B][U]Description[/U][/B],
 [U][B]Type[/B][/U], [B][U]Size[/U][/B]
, RowSource FROM Sheet1 ORDER BY ID;")

There are a few reserved words/tokens in your table/code, lines 4 and 5 above. These will eventully give you issues - especially if you upgrade to a newer version of Office/Access or attempt to push the data up to a mature RDBS (MySQL, SQL Server, Oracle, etc...) or even to SharePoint (IMHO:yuck).

You might find the following usefull:
Access 2007 reserved words and symbols
AllenBrowne- Problem names and reserved words in Access

[NeoPa]

Glad to see this one resolved Lysander old friend :-)

I would suggest that it is good practice (always has been but more important nowadays) to use DAO. and ADO. references explicitly in code. This is because they each use object names that are also used by the other. When not specified explicitly Access will default to one or other depending on version, and possibly even, other things I'm unaware of. However that is, I expect for reasons not clearly understood at this time (certainly not by me with current info), I believe the way Access defaulted this for you recently changed from one to the other and you were left with a confusing mess to deal with.

Knowing that you're already a fan of the Option Explicit in your coding, I expect you'll feel right at home taking this one on board. They're very similar concepts in my view.

All the best -Ade.