Why does Access 2000 SP 3 crash when using TableDefs.Append?

[dtecmeister]

I have the following code to add a MySQL ODBC 3.51 Driver linked table to my
database. It works in Office 2003 but crashes Access in Office 2000 and
corrupts the linked table manager and the database. We're running Windows XP.

Dim RT As Database
Dim tb As TableDef
Set RT = CurrentDb()
Set tb = RT.CreateTableDef("Group_" + NewData)
tb.SourceTableName = "Group_" + NewData
tb.Connect = "ODBC;DSN=Realtime;"
RT.TableDefs.Append tb
Me.Group.Undo
Me.Group.Requery
Me.Group.Value = NewData

The RT.TableDefs.Append tb line is where it crashes unrecoverable.

Any ideas on why this happens and how I can correct it?

 

[JonOfAllTrades]

I have the following code to add a MySQL ODBC 3.51 Driver linked table to my
database. It works in Office 2003 but crashes Access in Office 2000 and
corrupts the linked table manager and the database. We're running Windows XP.

Dim RT As Database
Dim tb As TableDef
Set RT = CurrentDb()
Set tb = RT.CreateTableDef("Group_" + NewData)
tb.SourceTableName = "Group_" + NewData
tb.Connect = "ODBC;DSN=Realtime;"
RT.TableDefs.Append tb
Me.Group.Undo
Me.Group.Requery
Me.Group.Value = NewData

The RT.TableDefs.Append tb line is where it crashes unrecoverable.

Any ideas on why this happens and how I can correct it?

I'm also using Access FE, MySQL BE, and I've had similar problems. I have't
used TableDefs, but would it be possible to use a pass-through query?
BTW, there is also an active Access-MySQL forum at
http://forums.mysql.com/list.php?65, they might be able to help you there too.

 

[dtecmeister]

Thanks for the info. I could change to a passthrough, but I'd much rather
figure out a way around this bug. Changing would require quite a bit of
rework in the code design opening room for bugs.

 

[david epsom dot com dot au]

IMHO, Access 2000 has the nasty habit of crashing instead of
reporting a simple exception. Check that the connect string
really works. Try updating an existing tabledef to the new
string instead of creating a new tabledef. Check that you
don't already have table with the new name.

Because your example works in 2003, check that your (VBA)
references are OK in the 2000 version on that PC.

(david)

 

[dtecmeister]

Thanks David. I tried changing the connect string. I'll have to test it on
one of the 2000 machines to see if it helps.

I'll post here the fix if I find it.