VBA error trying to export table/index SQL

[SarahEmmm]

I'm using Access 2000.

Found some code posted by a helpful Kiwi to export table and index sql
create statements to MySQL, which seemed like just what I wanted, which
is basically to ensure I have a master text file of the spec of my
database (it has no forms, only tables and indexes).

I am getting a runtime error, although it compiles fine (once I had
worked out that I needed to declare the db and index as DAO) - can
someone tell me what I have missed? I don't seem to have all the help
installed, which doesn't help either, and can't get the details of the
database properties.

Sub export_mysql()

' Exports the database contents into a file in mysql format
' IS NOT SELECTIVE! (exports ALL tables)

' version 1.00 August 1997

' INSTRUCTIONS
'Paste this function into an Access module of a database which has
the
'tables you want to export.

Dim i As Integer, j As Integer, k As Integer, f As Integer, x As
Integer
Dim fd As Integer, found As Integer, reccount As Integer
Dim s As String
Dim stuff As String
Dim iname As String, tname As String, istuff As String
Dim is_string As String
Dim tyyppi As String, pituus As Integer, comma As String
Dim row As String, it As String
Dim recset As Recordset
Dim idx As DAO.Index
Dim dbDatabase As DAO.Database
Dim fld As Field
Dim tdef As Recordset

Set dbDatabase = CurrentDb()

<code snipped>

'primary key and other index declaration
k = 0
For Each idx In dbDatabase.TableDefs(i).Indexes
'Check Primary property
k = k + 1
If idx.Primary Then
istuff = " PRIMARY KEY ("
Else
istuff = " KEY ("
End If
f = 0
'*** this line gives runtime error 13 - type mismatch
For Each fld In idx.Fields
'***
f = f + 1
iname = fld.Name
For j = 1 To Len(iname)
If j < Len(iname) Then
If Mid$(iname, j, 1) = " " Then
s = Left$(iname, j - 1)
s = s & "" & Right$(iname, Len(iname) - j)
j = j + 1
found = True
iname = s
End If
End If
Next j
istuff = istuff & iname
If f < idx.Fields.Count Then
istuff = istuff & ","
End If
Next fld

<rest of code snipped>

Many thanks!

 

['69 Camaro]

Hi, Sarah.

I am getting a runtime error, although it compiles fine (once I had
worked out that I needed to declare the db and index as DAO)

It's very unlikely that you need to declare a DAO.Database, because there's
only one Database class in your default, common, and uncommon libraries, so
there's no need to disambiguate this one from another Database class.
You'll need to disambiguate the Index class if you have the ADOX library
referenced, but I can't think of any other libraries that use the Index
class. So if you don't have the ADOX library referenced, then you don't
need to disambiguate this Index class from another one.

can
someone tell me what I have missed?

Recordset and Field need to be disambiguated from the ADO library's classes
of the same name, so prefix these classes with the DAO library. For
example:

Dim fld As DAO.Field
Dim tdef As DAO.Recordset

I don't seem to have all the help
installed, which doesn't help either, and can't get the details of the
database properties.

Access's online help wouldn't help you on this, even if it were installed.
Use the Object Browser to identify which class is in which library.

HTH.
Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[SarahEmmm]

Thanks Gunny!

I'll try that in a minute (got a big test running). But I only
identified the database and index variables as DAO because the original
code would not compile, so.. hopefully this will solve my problem.

 

['69 Camaro]

You're welcome!

Gunny

See http://www.QBuilt.com for all your database needs.
See http://www.Access.QBuilt.com for Microsoft Access tips.

 

[SarahEmmm]

Works perfectly now - thanks for the help!