PDA

Vollständige Version anzeigen : Benutzerdefinierter Typ nicht definiert


vinylist
08.02.2001, 00:16
Hi,

ich habe volgendes Problem!! Ich bekomme die Fehlermeldung "Benutzerdefinierter Typ nicht definiert"!!

Bei:

Dim dbase As Database, tdef As Recordset, i As Integer, fd As Integer, tname As String, j As Integer, iname As String
Dim s As String, found As Integer, stuff As String, idx As Index, k As Integer, f As Integer, fld As Field, istuff As String

genaugenommen bei:
Dim dbase As Database

und bei
idx As Index

Was kann ich tun??

Danke Björn

Adonis
08.02.2001, 15:52
Da Database und Index keine Standart Datentypen sind, nehme ich an, dass du dich auf ein benutzerdefinierten Datentyp bzw. ein Objekttyp beziehst (z.B. eine Klasse), den du vorher erstellt hast.

In diesem Fall kann ich mir nur vorstellen, dass du den Namen des Objekts bzw. des entsprechenden Datentyps irgendwie falsch geschrieben hast. Überprüfe das einfach nochmal.

Gruß Oli

Heathen
09.02.2001, 10:00
Hallo Björn,
Dein Problem liegt wohl darin, daß Dir der Verweis auf Microsoft DAO 3.6 Object Library fehlt. Dort sind diese Variablentypen verankert.

vinylist
09.02.2001, 10:09
Wie kann ich den Verweis auf die Microsoft DAO 3.6 Object Library setzen???? Ich nutze das VB was bei dem Access 2000 dabei ist!!

Danke Björn

Hier nochmal der source: (open source - Macht aus access-daten einen fertigen SQL dump zum importieren in eine sql database engine)

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Option</span> <span class="TOKEN">Compare</span> <span class="TOKEN">Database</span>
&nbsp;
<span class="TOKEN">Function</span> export_sql()
<span class="REM">'Exports the database contents into a file in sql format</span>
<span class="REM">'IS NOT SELECTIVE! (exports ALL tables)</span>
<span class="REM">'version 1.01 1997 - 2000</span>
<span class="REM">'by Brian Andrews</span>
<span class="REM">'InforMate</span>
<span class="REM">'info@informate.co.nz</span>
<span class="REM">'http://www.informate.co.nz</span>
<span class="REM">'INSTRUCTIONS</span>
<span class="REM">'Paste this function into a Microsoft Access module of a database that has the</span>
<span class="REM">'tables you want to export. Create a macro with the function RunCode and the</span>
<span class="REM">'argument export_sql (). Run the macro to start the export.</span>
<span class="REM">'Access 2000</span>
<span class="REM">'------------</span>
<span class="REM">'Under Tools, References ensure you have the following selected:</span>
<span class="REM">'Visual Basic for Applications</span>
<span class="REM">'Microsoft Access 9.0 Object Library</span>
<span class="REM">'Microsoft DAO 3.6 Object Library</span>
<span class="REM">'OLE Automation</span>
<span class="REM">'Microsoft Visual Basic for Applications Extensibility 5.3</span>
<span class="REM">'*** Unselect ActiveX Data Objects references!!!</span>
&nbsp;
<span class="TOKEN">Dim</span> dbase <span class="TOKEN">As</span> Database, tdef <span class="TOKEN">As</span> Recordset, i <span class="TOKEN">As</span> Integer, fd <span class="TOKEN">As</span> Integer, _
tname <span class="TOKEN">As</span> String, j <span class="TOKEN">As</span> Integer, iname <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> s <span class="TOKEN">As</span> String, found <span class="TOKEN">As</span> Integer, stuff <span class="TOKEN">As</span> String, idx <span class="TOKEN">As</span> Index, _
k <span class="TOKEN">As</span> Integer, f <span class="TOKEN">As</span> Integer, fld <span class="TOKEN">As</span> Field, istuff <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Set</span> dbase = CurrentDb()
&nbsp;
<span class="REM">'Open the file to export the defintions and data to. Change this to suit your needs ****</span>
<span class="TOKEN">Open</span> &quot;c:\haitime\database.sql&quot; <span class="TOKEN">For</span> <span class="TOKEN">Output</span> <span class="TOKEN">As</span> #1
<span class="TOKEN">Print</span> #1, &quot;------------------------------------------------&quot;
<span class="TOKEN">Print</span> #1, &quot;# Converted from MS <span class="TOKEN">Access</span> to SQL&quot;
<span class="TOKEN">Print</span> #1, &quot;# by Brian Andrews, &copy; InforMate (www.informate.co.nz),&quot; &amp; _
&quot;brian@informate.co.nz, 1997-2000&quot;
<span class="TOKEN">Print</span> #1, &quot;-------------------------------------------------&quot;
<span class="TOKEN">Print</span> #1, &quot;# Changed from Living Solution - Software for www.haitime.com - auctions&quot;
<span class="TOKEN">Print</span> #1, &quot;-------------------------------------------------&quot;
&nbsp;
<span class="REM">'Go through the table definitions</span>
<span class="TOKEN">For</span> i = 0 <span class="TOKEN">To</span> dbase.TableDefs.Count - 1
<span class="REM">' Let's take only the visible tables</span>
<span class="TOKEN">If</span> ((dbase.TableDefs(i).Attributes <span class="TOKEN">And</span> DB_SYSTEMOBJECT) <span class="TOKEN">Or</span> _
(dbase.TableDefs(i).Attributes <span class="TOKEN">And</span> DB_HIDDENOBJECT)) <span class="TOKEN">Then</span>
<span class="TOKEN">Else</span>
<span class="REM">' We DROP the table if it already exists</span>
<span class="REM">' and then create it again</span>
tname = &quot;&quot; &amp; dbase.TableDefs(i).Name
<span class="REM">'remove spaces from tablename</span>
<span class="TOKEN">For</span> j = 1 <span class="TOKEN">To</span> Len(tname)
<span class="TOKEN">If</span> j &lt; Len(tname) <span class="TOKEN">Then</span>
<span class="TOKEN">If</span> Mid$(tname, j, 1) = &quot; &quot; <span class="TOKEN">Then</span>
s = Left$(tname, j - 1)
s = s &amp; &quot;&quot; &amp; Right$(tname, Len(tname) - j)
j = j + 1
found = <span class="TOKEN">True</span>
tname = s
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> j
<span class="REM">'restrict tablename to 19 chars</span>
tname = Left$(tname, 19)
<span class="REM">'comment out these lines if the table doesn't exist or else create it first</span>
<span class="TOKEN">Print</span> #1, &quot;&quot;
<span class="TOKEN">Print</span> #1, &quot;&quot;
<span class="TOKEN">Print</span> #1, &quot;DROP TABLE IF EXISTS &quot; &amp; tname &amp; &quot;;&quot;
<span class="TOKEN">Print</span> #1,
<span class="TOKEN">Print</span> #1, &quot;CREATE TABLE &quot; &amp; tname &amp; &quot; (&quot;
&nbsp;
<span class="REM">' Step through all the fields in the table</span>
<span class="TOKEN">For</span> fd = 0 <span class="TOKEN">To</span> dbase.TableDefs(i).Fields.Count - 1
<span class="TOKEN">Dim</span> tyyppi <span class="TOKEN">As</span> String, pituus <span class="TOKEN">As</span> Integer, comma <span class="TOKEN">As</span> <span class="TOKEN">String</span>
Select Case dbase.TableDefs(i).Fields(fd).Type
Case DB_BOOLEAN
tyyppi = &quot;SMALLINT&quot;
Case DB_INTEGER
tyyppi = &quot;SMALLINT&quot;
Case DB_BYTE
tyyppi = &quot;TINYBLOB&quot;
Case DB_LONG
<span class="TOKEN">If</span> dbase.TableDefs(i).Fields(fd).Attributes = 17 <span class="TOKEN">Then</span>
tyyppi = &quot;INT AUTO_INCREMENT&quot;
<span class="TOKEN">Else</span>
tyyppi = &quot;INT&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
Case DB_DOUBLE
tyyppi = &quot;DOUBLE&quot;
Case DB_SINGLE <span class="REM">'</span>
tyyppi = &quot;REAL&quot;
Case DB_CURRENCY
tyyppi = &quot;DOUBLE (8,4)&quot;
Case DB_TEXT
pituus = dbase.TableDefs(i).Fields(fd).Size
tyyppi = &quot;CHAR (&quot; &amp; pituus &amp; &quot;)&quot;
Case dbAutoIncrField
<span class="REM"> ' tyyppi = &quot;INT NOT NULL AUTO_INCREMENT&quot;</span>
<span class="REM"> 'Access Date fields are set as the sql date type - you can change this to</span>
<span class="REM"> 'DATETIME if you prefer.</span>
Case DB_DATE
tyyppi = &quot;DATE&quot;
Case DB_MEMO, DB_LONGBINARY
tyyppi = &quot;BLOB&quot;
<span class="TOKEN">End</span> Select
&nbsp;
<span class="REM">'Print the field definition</span>
<span class="REM">'remove spaces from fieldname</span>
stuff = &quot;&quot; &amp; dbase.TableDefs(i).Fields(fd).Name
<span class="REM">'we had a table called Index which sql doesn't like</span>
<span class="TOKEN">If</span> stuff = &quot;Index&quot; <span class="TOKEN">Then</span> stuff = &quot;Indexm&quot;
&nbsp;
<span class="TOKEN">For</span> j = 1 <span class="TOKEN">To</span> Len(stuff)
<span class="TOKEN">If</span> j &lt; Len(stuff) <span class="TOKEN">Then</span>
<span class="TOKEN">If</span> Mid$(stuff, j, 1) = &quot; &quot; <span class="TOKEN">Then</span>
s = Left$(stuff, j - 1)
s = s &amp; &quot;&quot; &amp; Right$(stuff, Len(stuff) - j)
j = j + 1
found = <span class="TOKEN">True</span>
stuff = s
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> j
stuff = Left$(stuff, 19)
<span class="REM">'not null</span>
k = 0
<span class="TOKEN">For</span> <span class="TOKEN">Each</span> idx <span class="TOKEN">In</span> dbase.TableDefs(i).Indexes
<span class="TOKEN">If</span> (dbase.TableDefs(i).Indexes(k).Fields(0).Name = _
dbase.TableDefs(i).Fields(fd).Name) <span class="TOKEN">Then</span>
tyyppi = tyyppi &amp; &quot; NOT NULL&quot;
<span class="TOKEN">Exit For</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
k = k + 1
<span class="TOKEN">Next</span>
&nbsp;
<span class="REM">'default value</span>
<span class="TOKEN">If</span> (<span class="TOKEN">Not</span> (IsNull(dbase.TableDefs(i).Fields(fd).DefaultValue)) <span class="TOKEN">And</span> _
dbase.TableDefs(i).Fields(fd).DefaultValue &lt;&gt; &quot;&quot;) <span class="TOKEN">Then</span>
&nbsp;
<span class="TOKEN">If</span> dbase.TableDefs(i).Fields(fd).Required = <span class="TOKEN">False</span> <span class="TOKEN">Then</span>
tyyppi = tyyppi &amp; &quot; NOT NULL &quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
&nbsp;
<span class="TOKEN">If</span> Left$(dbase.TableDefs(i).Fields(fd).DefaultValue, 1) = Chr(34) <span class="TOKEN">Then</span>
tyyppi = tyyppi &amp; &quot; DEFAULT '&quot; &amp; _
Mid$(dbase.TableDefs(i).Fields(fd).DefaultValue, 2, _
Len(dbase.TableDefs(i).Fields(fd).DefaultValue) - 2) &amp; &quot;'&quot;
<span class="TOKEN">Else</span>
tyyppi = tyyppi &amp; &quot; DEFAULT &quot; &amp; dbase.TableDefs(i).Fields(fd).DefaultValue
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
&nbsp;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
&nbsp;
<span class="REM">'print out field info</span>
comma = &quot;,&quot;
<span class="TOKEN">If</span> fd = dbase.TableDefs(i).Fields.Count - 1 <span class="TOKEN">Then</span>
<span class="TOKEN">If</span> dbase.TableDefs(i).Indexes.Count = 0 <span class="TOKEN">Then</span>
comma = &quot;&quot;
<span class="TOKEN">Else</span>
comma = &quot;,&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Print</span> #1, &quot; &quot; &amp; stuff &amp; &quot; &quot; &amp; tyyppi &amp; comma
<span class="TOKEN">Next</span> fd
&nbsp;
<span class="REM">'primary key and other index declaration</span>
k = 0
&nbsp;
<span class="TOKEN">For</span> <span class="TOKEN">Each</span> idx <span class="TOKEN">In</span> dbase.TableDefs(i).Indexes
<span class="REM">'Check Primary property</span>
k = k + 1
<span class="TOKEN">If</span> idx.Primary <span class="TOKEN">Then</span>
istuff = &quot; PRIMARY KEY (&quot;
<span class="TOKEN">Else</span>
istuff = &quot; KEY (&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
f = 0
&nbsp;
<span class="TOKEN">For</span> <span class="TOKEN">Each</span> fld <span class="TOKEN">In</span> idx.Fields
f = f + 1
iname = fld.Name
<span class="TOKEN">For</span> j = 1 <span class="TOKEN">To</span> Len(iname)
<span class="TOKEN">If</span> j &lt; Len(iname) <span class="TOKEN">Then</span>
<span class="TOKEN">If</span> Mid$(iname, j, 1) = &quot; &quot; <span class="TOKEN">Then</span>
s = Left$(iname, j - 1)
s = s &amp; &quot;&quot; &amp; Right$(iname, Len(iname) - j)
j = j + 1
found = <span class="TOKEN">True</span>
iname = s
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> j
istuff = istuff &amp; iname
<span class="TOKEN">If</span> f &lt; idx.Fields.Count <span class="TOKEN">Then</span>
istuff = istuff &amp; &quot;,&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> fld
&nbsp;
<span class="TOKEN">If</span> k &lt; dbase.TableDefs(i).Indexes.Count <span class="TOKEN">Then</span>
<span class="TOKEN">Print</span> #1, istuff &amp; &quot;),&quot;
<span class="TOKEN">Else</span>
<span class="TOKEN">Print</span> #1, istuff &amp; &quot;)&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> idx
<span class="TOKEN">Print</span> #1, &quot;);&quot;
<span class="TOKEN">Print</span> #1, &quot;&quot;
&nbsp;
&nbsp;
<span class="TOKEN">Dim</span> recset <span class="TOKEN">As</span> Recordset
<span class="TOKEN">Dim</span> row <span class="TOKEN">As</span> String, it <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> is_string <span class="TOKEN">As</span> String, reccount <span class="TOKEN">As</span> Integer, x <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Set</span> recset = dbase.OpenRecordset(dbase.TableDefs(i).Name)
reccount = recset.RecordCount
<span class="TOKEN">If</span> reccount &lt;&gt; 0 <span class="TOKEN">Then</span>
<span class="REM">' Step through the rows in the table</span>
recset.MoveFirst
<span class="TOKEN">Do</span> <span class="TOKEN">Until</span> recset.EOF
row = &quot;INSERT INTO &quot; &amp; tname &amp; &quot; VALUES (&quot;
<span class="REM">' Go through the fields in the row</span>
<span class="TOKEN">For</span> fd = 0 <span class="TOKEN">To</span> recset.Fields.Count - 1
is_string = &quot;&quot;
stuff = &quot;&quot; &amp; recset.Fields(fd).Value
Select Case recset.Fields(fd).Type
Case DB_BOOLEAN
<span class="REM">'true fields are set to 1, false are set to 0</span>
<span class="TOKEN">If</span> recset.Fields(fd).Value = <span class="TOKEN">True</span> <span class="TOKEN">Then</span>
stuff = &quot;0&quot;
<span class="TOKEN">Else</span>
stuff = &quot;1&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
&nbsp;
Case DB_TEXT, DB_MEMO, 15, DB_LONGBINARY
is_string = &quot;'&quot;
Case DB_DATE
is_string = &quot;'&quot;
&nbsp;
<span class="REM">'format date fields to YYYY-MM-DD. You may want to add time formatting as</span>
<span class="REM">'well if you have declared DATE fields as DATETIME</span>
<span class="TOKEN">If</span> stuff &lt;&gt; &quot;&quot; <span class="TOKEN">And</span> <span class="TOKEN">Not</span> (IsNull(stuff)) <span class="TOKEN">Then</span>
stuff = Format(stuff, &quot;YYYY-MM-DD&quot;)
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Case Else</span>
&nbsp;
<span class="REM">'default empty number fields to 0 - comment this out if you want</span>
<span class="TOKEN">If</span> stuff = &quot;&quot; <span class="TOKEN">Then</span>
stuff = &quot;0&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> Select
&nbsp;
<span class="REM">'**** escape single quotes</span>
x = InStr(stuff, &quot;'&quot;)
&nbsp;
<span class="TOKEN">While</span> x &lt;&gt; 0
s = Left$(stuff, x - 1)
s = s &amp; &quot;\&quot; &amp; Right$(stuff, Len(stuff) - x + 1)
stuff = s
x = InStr(x + 2, stuff, &quot;'&quot;)
<span class="TOKEN">Wend</span>
&nbsp;
<span class="REM">'**** convert returns to</span>
<span class="REM">'s</span>
x = InStr(stuff, Chr(13))
&nbsp;
<span class="TOKEN">While</span> x &lt;&gt; 0
s = Left$(stuff, x - 1)
<span class="TOKEN">If</span> x = Len(stuff) <span class="TOKEN">Then</span>
s = s &amp; &quot;&quot;
<span class="TOKEN">Else</span>
s = s &amp; &quot;&quot; &amp; Right$(stuff, Len(stuff) - x - 1)
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
stuff = s
x = InStr(x + 2, stuff, Chr(13))
<span class="TOKEN">Wend</span>
&nbsp;
row = row &amp; is_string &amp; stuff &amp; is_string
<span class="TOKEN">If</span> fd &lt; recset.Fields.Count - 1 <span class="TOKEN">Then</span>
row = row &amp; &quot;,&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> fd
&nbsp;
<span class="REM">' Add trailers and print</span>
row = row &amp; &quot;);&quot;
<span class="TOKEN">Print</span> #1, row
&nbsp;
<span class="REM">' Move to the next row</span>
recset.MoveNext
<span class="TOKEN">Loop</span>
&nbsp;
recset.Close
<span class="TOKEN">Set</span> recset = <span class="TOKEN">Nothing</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> i
&nbsp;
<span class="TOKEN">Close</span> #1
dbase.Close
<span class="TOKEN">Set</span> dbase = <span class="TOKEN">Nothing</span>
<span class="TOKEN">End</span> <span class="TOKEN">Function</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

<font size="1" face="Century Gothic">Moderatorenanmerkung: Überarbeitung, durch den Wechsel der Forensoftware zum 01.01.2003 verursacht: in diesem Beitrag Link(s) korrigiert.
jinx – 09.07.2003</font>

Heathen
09.02.2001, 16:05
Dein Sourcecode hat wohl nur den Fehler, daß Du geschrieben hast:
Dim tdef as Recordset und recset as Recordset

Da müßte stehen:
Dim tdef as DAO.Recordset und recset as DAO.Recordset

Ansonsten fügt man Verweise über den Menüpunkt "Extras-Verweise" hinzu

vinylist
09.02.2001, 17:49
Ne trotzdem noch der alte Fehler!


Björn

Manuela Kulpa
09.02.2001, 18:39
Hallo Björn!

Anbei die Überarbeitung deiner Sourcen (formatiert, damit man es ein klein wenig besser lesen kann). Grundsätzlich solltest du unter A2K bei Datenbankzugriffen die Bibliothek mit angeben. Zudem habe ich alle Integer-Werte in Long-Werte umgewandelt (da sonst, je nach Datenmenge, ein Überlauf stattfinden würde). Ach ja, du solltest auch noch den Zielpfad sowie die Zieldatei modifizieren (Änderungen sind hervorgehoben).

Bei mir läuft die Funktion tadellos!

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Function</span> export_sql()
<span class="REM">'Exports the database contents into a file in sql format</span>
<span class="REM">'IS NOT SELECTIVE! (exports ALL tables)</span>
<span class="REM">'version 1.01 1997 - 2000</span>
<span class="REM">'by Brian Andrews</span>
<span class="REM">'InforMate</span>
<span class="REM">'info@informate.co.nz</span>
<span class="REM">'http://www.informate.co.nz</span>
&nbsp;
<span class="REM">'INSTRUCTIONS</span>
<span class="REM">'Paste this function into a Microsoft Access module of a database that has the</span>
<span class="REM">'tables you want to export. Create a macro with the function RunCode and the</span>
<span class="REM">'argument export_sql (). Run the macro to start the export.</span>
&nbsp;
<span class="REM">'Access 2000</span>
<span class="REM">'------------</span>
<span class="REM">'Under Tools, References ensure you have the following selected:</span>
<span class="REM">'Visual Basic for Applications</span>
<span class="REM">'Microsoft Access 9.0 Object Library</span>
<span class="REM">'Microsoft DAO 3.6 Object Library</span>
<span class="REM">'OLE Automation</span>
<span class="REM">'Microsoft Visual Basic for Applications Extensibility 5.3</span>
&nbsp;
<span class="REM">'*** Unselect ActiveX Data Objects references!!!</span>
<span class="TOKEN">Dim</span> dbase <span class="TOKEN">As</span> DAO.Database
<span class="TOKEN">Dim</span> tdef <span class="TOKEN">As</span> DAO.Recordset
<span class="TOKEN">Dim</span> idx <span class="TOKEN">As</span> DAO.Index
<span class="TOKEN">Dim</span> fld <span class="TOKEN">As</span> DAO.Field
<span class="TOKEN">Dim</span> i <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
<span class="TOKEN">Dim</span> fd <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
<span class="TOKEN">Dim</span> tname <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> j <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
<span class="TOKEN">Dim</span> iname <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> s <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> found <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
<span class="TOKEN">Dim</span> stuff <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> k <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
<span class="TOKEN">Dim</span> f <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
<span class="TOKEN">Dim</span> istuff <span class="TOKEN">As</span> <span class="TOKEN">String</span>
&nbsp;
<span class="TOKEN">Set</span> dbase = CurrentDb()
&nbsp;
<span class="REM"> 'Open the file to export the defintions and data to. Change this to suit your needs ****</span>
<span class="REM"> ' Hier sollte dein Zielpfad und deine Zieldatei stehen</span>
<span class="TOKEN">Open</span> &quot;E:\Download\database.sql&quot; <span class="TOKEN">For</span> <span class="TOKEN">Output</span> <span class="TOKEN">As</span> #1
&nbsp;
<span class="TOKEN">Print</span> #1, &quot;--------------------------------------------------------&quot;
<span class="TOKEN">Print</span> #1, &quot;# Converted from MS <span class="TOKEN">Access</span> to SQL&quot;
<span class="TOKEN">Print</span> #1, &quot;# by Brian Andrews, &copy; InforMate (www.informate.co.nz),&quot; &amp; _
&quot;brian@informate.co.nz, 1997-2000&quot;
<span class="TOKEN">Print</span> #1, &quot;--------------------------------------------------------&quot;
<span class="TOKEN">Print</span> #1, &quot;# Changed from Living Solution - Software for www.haitime.com - auctions&quot;
<span class="TOKEN">Print</span> #1, &quot;--------------------------------------------------------&quot;
&nbsp;
<span class="REM"> 'Go through the table definitions</span>
<span class="TOKEN">For</span> i = 0 <span class="TOKEN">To</span> dbase.TableDefs.Count - 1
<span class="REM"> ' Let's take only the visible tables</span>
<span class="TOKEN">If</span> ((dbase.TableDefs(i).Attributes <span class="TOKEN">And</span> DB_SYSTEMOBJECT) <span class="TOKEN">Or</span> _
(dbase.TableDefs(i).Attributes <span class="TOKEN">And</span> DB_HIDDENOBJECT)) <span class="TOKEN">Then</span>
<span class="TOKEN">Else</span>
<span class="REM"> ' We DROP the table if it already exists</span>
<span class="REM"> ' and then create it again</span>
tname = &quot;&quot; &amp; dbase.TableDefs(i).Name
<span class="REM"> 'remove spaces from tablename</span>
<span class="TOKEN">For</span> j = 1 <span class="TOKEN">To</span> Len(tname)
<span class="TOKEN">If</span> j &lt; Len(tname) <span class="TOKEN">Then</span>
<span class="TOKEN">If</span> Mid$(tname, j, 1) = &quot; &quot; <span class="TOKEN">Then</span>
s = Left$(tname, j - 1)
s = s &amp; &quot;&quot; &amp; Right$(tname, Len(tname) - j)
j = j + 1
found = <span class="TOKEN">True</span>
tname = s
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> j
<span class="REM"> 'restrict tablename to 19 chars</span>
tname = Left$(tname, 19)
<span class="REM"> 'comment out these lines if the table doesn't exist or else create it first</span>
&nbsp;
<span class="TOKEN">Print</span> #1, &quot;&quot;
<span class="TOKEN">Print</span> #1, &quot;&quot;
<span class="TOKEN">Print</span> #1, &quot;DROP TABLE IF EXISTS &quot; &amp; tname &amp; &quot;;&quot;
<span class="TOKEN">Print</span> #1,
<span class="TOKEN">Print</span> #1, &quot;CREATE TABLE &quot; &amp; tname &amp; &quot; (&quot;
&nbsp;
<span class="REM"> ' Step through all the fields in the table</span>
<span class="TOKEN">For</span> fd = 0 <span class="TOKEN">To</span> dbase.TableDefs(i).Fields.Count - 1
<span class="TOKEN">Dim</span> tyyppi <span class="TOKEN">As</span> String, pituus <span class="TOKEN">As</span> Long, comma <span class="TOKEN">As</span> <span class="TOKEN">String</span>
Select Case dbase.TableDefs(i).Fields(fd).Type
Case DB_BOOLEAN
tyyppi = &quot;SMALLINT&quot;
Case DB_LONG
tyyppi = &quot;SMALLINT&quot;
Case DB_BYTE
tyyppi = &quot;TINYBLOB&quot;
Case DB_LONG
<span class="TOKEN">If</span> dbase.TableDefs(i).Fields(fd).Attributes = 17 <span class="TOKEN">Then</span>
tyyppi = &quot;INT AUTO_INCREMENT&quot;
<span class="TOKEN">Else</span>
tyyppi = &quot;INT&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
Case DB_DOUBLE
tyyppi = &quot;DOUBLE&quot;
Case DB_SINGLE <span class="REM">'</span>
tyyppi = &quot;REAL&quot;
Case DB_CURRENCY
tyyppi = &quot;DOUBLE (8,4)&quot;
Case DB_TEXT
pituus = dbase.TableDefs(i).Fields(fd).Size
tyyppi = &quot;CHAR (&quot; &amp; pituus &amp; &quot;)&quot;
Case dbAutoIncrField
<span class="REM"> ' tyyppi = &quot;INT NOT NULL AUTO_INCREMENT&quot;</span>
<span class="REM"> 'Access Date fields are set as the sql date type - you can change this to</span>
<span class="REM"> 'DATETIME if you prefer.</span>
Case DB_DATE
tyyppi = &quot;DATE&quot;
Case DB_MEMO, DB_LONGBINARY
tyyppi = &quot;BLOB&quot;
<span class="TOKEN">End</span> Select
&nbsp;
<span class="REM"> 'Print the field definition</span>
<span class="REM"> 'remove spaces from fieldname</span>
stuff = &quot;&quot; &amp; dbase.TableDefs(i).Fields(fd).Name
<span class="REM"> 'we had a table called Index which sql doesn't like</span>
<span class="TOKEN">If</span> stuff = &quot;Index&quot; <span class="TOKEN">Then</span> stuff = &quot;Indexm&quot;
<span class="TOKEN">For</span> j = 1 <span class="TOKEN">To</span> Len(stuff)
<span class="TOKEN">If</span> j &lt; Len(stuff) <span class="TOKEN">Then</span>
<span class="TOKEN">If</span> Mid$(stuff, j, 1) = &quot; &quot; <span class="TOKEN">Then</span>
s = Left$(stuff, j - 1)
s = s &amp; &quot;&quot; &amp; Right$(stuff, Len(stuff) - j)
j = j + 1
found = <span class="TOKEN">True</span>
stuff = s
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> j
stuff = Left$(stuff, 19)
<span class="REM"> 'not null</span>
k = 0
&nbsp;
<span class="TOKEN">For</span> <span class="TOKEN">Each</span> idx <span class="TOKEN">In</span> dbase.TableDefs(i).Indexes
<span class="TOKEN">If</span> (dbase.TableDefs(i).Indexes(k).Fields(0).Name = _
dbase.TableDefs(i).Fields(fd).Name) <span class="TOKEN">Then</span>
tyyppi = tyyppi &amp; &quot; NOT NULL&quot;
<span class="TOKEN">Exit For</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
k = k + 1
<span class="TOKEN">Next</span>
&nbsp;
<span class="REM"> 'default value</span>
<span class="TOKEN">If</span> (<span class="TOKEN">Not</span> (IsNull(dbase.TableDefs(i).Fields(fd).DefaultValue)) <span class="TOKEN">And</span> _
dbase.TableDefs(i).Fields(fd).DefaultValue &lt;&gt; &quot;&quot;) <span class="TOKEN">Then</span>
<span class="TOKEN">If</span> dbase.TableDefs(i).Fields(fd).Required = <span class="TOKEN">False</span> <span class="TOKEN">Then</span>
tyyppi = tyyppi &amp; &quot; NOT NULL &quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">If</span> Left$(dbase.TableDefs(i).Fields(fd).DefaultValue, 1) = Chr(34) <span class="TOKEN">Then</span>
tyyppi = tyyppi &amp; &quot; DEFAULT '&quot; &amp; _
Mid$(dbase.TableDefs(i).Fields(fd).DefaultValue, 2, _
Len(dbase.TableDefs(i).Fields(fd).DefaultValue) - 2) &amp; &quot;'&quot;
<span class="TOKEN">Else</span>
tyyppi = tyyppi &amp; &quot; DEFAULT &quot; &amp; dbase.TableDefs(i).Fields(fd).DefaultValue
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
&nbsp;
<span class="REM"> 'print out field info</span>
comma = &quot;,&quot;
<span class="TOKEN">If</span> fd = dbase.TableDefs(i).Fields.Count - 1 <span class="TOKEN">Then</span>
<span class="TOKEN">If</span> dbase.TableDefs(i).Indexes.Count = 0 <span class="TOKEN">Then</span>
comma = &quot;&quot;
<span class="TOKEN">Else</span>
comma = &quot;,&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
&nbsp;
<span class="TOKEN">Print</span> #1, &quot; &quot; &amp; stuff &amp; &quot; &quot; &amp; tyyppi &amp; comma
<span class="TOKEN">Next</span> fd
&nbsp;
<span class="REM"> 'primary key and other index declaration</span>
k = 0
<span class="TOKEN">For</span> <span class="TOKEN">Each</span> idx <span class="TOKEN">In</span> dbase.TableDefs(i).Indexes
<span class="REM"> 'Check Primary property</span>
k = k + 1
<span class="TOKEN">If</span> idx.Primary <span class="TOKEN">Then</span>
istuff = &quot; PRIMARY KEY (&quot;
<span class="TOKEN">Else</span>
istuff = &quot; KEY (&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
f = 0
<span class="TOKEN">For</span> <span class="TOKEN">Each</span> fld <span class="TOKEN">In</span> idx.Fields
f = f + 1
iname = fld.Name
<span class="TOKEN">For</span> j = 1 <span class="TOKEN">To</span> Len(iname)
<span class="TOKEN">If</span> j &lt; Len(iname) <span class="TOKEN">Then</span>
<span class="TOKEN">If</span> Mid$(iname, j, 1) = &quot; &quot; <span class="TOKEN">Then</span>
s = Left$(iname, j - 1)
s = s &amp; &quot;&quot; &amp; Right$(iname, Len(iname) - j)
j = j + 1
found = <span class="TOKEN">True</span>
iname = s
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> j
istuff = istuff &amp; iname
<span class="TOKEN">If</span> f &lt; idx.Fields.Count <span class="TOKEN">Then</span>
istuff = istuff &amp; &quot;,&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> fld
<span class="TOKEN">If</span> k &lt; dbase.TableDefs(i).Indexes.Count <span class="TOKEN">Then</span>
<span class="TOKEN">Print</span> #1, istuff &amp; &quot;),&quot;
<span class="TOKEN">Else</span>
<span class="TOKEN">Print</span> #1, istuff &amp; &quot;)&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> idx
&nbsp;
<span class="TOKEN">Print</span> #1, &quot;);&quot;
<span class="TOKEN">Print</span> #1, &quot;&quot;
&nbsp;
<span class="TOKEN">Dim</span> recset <span class="TOKEN">As</span> DAO.Recordset
<span class="TOKEN">Dim</span> row <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> it <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> is_string <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> reccount <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
<span class="TOKEN">Dim</span> x <span class="TOKEN">As</span> <span class="TOKEN">Long</span>
&nbsp;
<span class="TOKEN">Set</span> recset = dbase.OpenRecordset(dbase.TableDefs(i).Name)
reccount = recset.RecordCount
<span class="TOKEN">If</span> reccount &lt;&gt; 0 <span class="TOKEN">Then</span>
<span class="REM"> ' Step through the rows in the table</span>
recset.MoveFirst
<span class="TOKEN">Do</span> <span class="TOKEN">Until</span> recset.EOF
row = &quot;INSERT INTO &quot; &amp; tname &amp; &quot; VALUES (&quot;
<span class="REM"> ' Go through the fields in the row</span>
<span class="TOKEN">For</span> fd = 0 <span class="TOKEN">To</span> recset.Fields.Count - 1
is_string = &quot;&quot;
stuff = &quot;&quot; &amp; recset.Fields(fd).Value
Select Case recset.Fields(fd).Type
Case DB_BOOLEAN
<span class="REM"> 'true fields are set to 1, false are set to 0</span>
<span class="TOKEN">If</span> recset.Fields(fd).Value = <span class="TOKEN">True</span> <span class="TOKEN">Then</span>
stuff = &quot;0&quot;
<span class="TOKEN">Else</span>
stuff = &quot;1&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
Case DB_TEXT, DB_MEMO, 15, DB_LONGBINARY
is_string = &quot;'&quot;
Case DB_DATE
is_string = &quot;'&quot;
<span class="REM"> 'format date fields to YYYY-MM-DD. You may want to add time formatting as</span>
<span class="REM"> 'well if you have declared DATE fields as DATETIME</span>
<span class="TOKEN">If</span> stuff &lt;&gt; &quot;&quot; <span class="TOKEN">And</span> <span class="TOKEN">Not</span> (IsNull(stuff)) <span class="TOKEN">Then</span>
stuff = Format(stuff, &quot;YYYY-MM-DD&quot;)
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Case Else</span>
<span class="REM"> 'default empty number fields to 0 - comment this out if you want</span>
<span class="TOKEN">If</span> stuff = &quot;&quot; <span class="TOKEN">Then</span>
stuff = &quot;0&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> Select
&nbsp;
<span class="REM"> '**** escape single quotes</span>
x = InStr(stuff, &quot;'&quot;)
<span class="TOKEN">While</span> x &lt;&gt; 0
s = Left$(stuff, x - 1)
s = s &amp; &quot;\&quot; &amp; Right$(stuff, Len(stuff) - x + 1)
stuff = s
x = InStr(x + 2, stuff, &quot;'&quot;)
<span class="TOKEN">Wend</span>
&nbsp;
<span class="REM"> '**** convert returns to</span>
<span class="REM"> 's</span>
x = InStr(stuff, Chr(13))
<span class="TOKEN">While</span> x &lt;&gt; 0
s = Left$(stuff, x - 1)
<span class="TOKEN">If</span> x = Len(stuff) <span class="TOKEN">Then</span>
s = s &amp; &quot;&quot;
<span class="TOKEN">Else</span>
s = s &amp; &quot;&quot; &amp; Right$(stuff, Len(stuff) - x - 1)
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
stuff = s
x = InStr(x + 2, stuff, Chr(13))
<span class="TOKEN">Wend</span>
row = row &amp; is_string &amp; stuff &amp; is_string
<span class="TOKEN">If</span> fd &lt; recset.Fields.Count - 1 <span class="TOKEN">Then</span>
row = row &amp; &quot;,&quot;
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> fd
<span class="REM"> ' Add trailers and print</span>
row = row &amp; &quot;);&quot;
<span class="TOKEN">Print</span> #1, row
<span class="REM"> ' Move to the next row</span>
recset.MoveNext
<span class="TOKEN">Loop</span>
recset.Close
<span class="TOKEN">Set</span> recset = <span class="TOKEN">Nothing</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">End</span> <span class="TOKEN">If</span>
<span class="TOKEN">Next</span> i
<span class="TOKEN">Close</span> #1
dbase.Close
<span class="TOKEN">Set</span> dbase = <span class="TOKEN">Nothing</span>
<span class="TOKEN">End</span> <span class="TOKEN">Function</span></pre></div>
Code eingefügt mit dem MOF Code Converter (http://www.ms-office-forum.net/forum/codeconverter.php)

<font size="1" face="Century Gothic">Moderatorenanmerkung: Überarbeitung, durch den Wechsel der Forensoftware zum 01.01.2003 verursacht: in diesem Beitrag Link(s) korrigiert.
jinx – 09.07.2003</font>

vinylist
09.02.2001, 18:54
Hey danke für deine super Hilfe bei meinem Problem!!! Allerdings habe ich weiterhin den selben Fehler im Kompilat!!

"Benutzerdefinierte Funktion nicht definiert"

Dim dbase As DAO.Database
Dim tdef As DAO.Recordset
Dim idx As DAO.Index
Dim fld As DAO.Field usw...

Was kann das noch sein???

Björn

vinylist
09.02.2001, 18:55
Sorry es heißt!!

Benutzerdefinierter Typ nicht definiert

Björn

Manuela Kulpa
10.02.2001, 08:18
Hallo Björn!

Eigentlich steht es ja in der Beschreibung der Funktion ;). Du musst die DAO 3.6 Bibliothek einbinden.
OK, machen wir mal eine kleine Schritt für Schritt Anleitung:

1. Öffne ein Modul bzw. begib dich in den Entwurf der Funktion
2. Gehe dort in das Menü Extras und wähle den Menüpunkt Verweise
3. Suche dort folgende Bibliothek: Microsoft DAO 3.6 Object Library und aktiviere sie
4. Kontrolliere ob folgende Verweise auch eingebunden sind, wenn nein, aktiviere Sie:

Visual Basic For Applications
Microsoft Access 9.0 Object Library
OLE Automation

So, das müsste es gewesen sein. In der Hoffnung, dass es nun bald bei dir klappt

mit vielen Grüßen

vinylist
10.02.2001, 10:15
Hey du bist echt ne Maus!!

Ich werde mich gleich um die Anforderungen kümmern!! Dann wird es schon klapppen! Sorry ich komme aus der PHP/SQL welt und habe echt noch nieeemals mit VB gearbeitet!!

Ciao dein Mäuserich Björn *gg*

testjdkjflds
11.12.2008, 10:01
Ich finde unter Extras den Punkt Verweise nicht

Hotte
11.12.2008, 12:24
Hi,

in VB ist der Punkt Verweise... unter Projekt ..

Hier ging es wohl um VBA.

Das "System" selbst ist aber - in diesem Falle - identisch.

Holmes34
10.01.2011, 09:00
Hallo Leute,

erstma ein verspätetes Frohes Neues :) habe ein ähnliches Problem wie vinylist. Wollte Gerade eine Excel Tabelle in Access sichern beim Importieren traten immer wieder Import Fehler auf die ich nicht alle per Hand korrigieren wollte. Also griff ich zu dem Access-VBA von Bernd Held und wollte an Hand von seinem Code die Sicherung vornehmen. Obwohl ich den Verweis auf die Microsoft DAO 3.6 Object Library gesetzt habe bringt er mir bei der Variablendeklaration: "Dim objXlApp As Excel.Application" den Fehler: "Benutzerdefinierter Typ nicht definiert" ich benutze Office2003 kann der Fehler vielleicht damit zusammenhängen? Anbei der Code:

Option Compare Database

Sub ExcelDatenSichern()
Dim db As Database
Dim rst As Recordset
Dim str As String
Dim strPfad As String
Dim objXlApp As Excel.Application
Dim objTabelle As Worksheet
Dim rngZelle As Range

Set db = CurrentDb
Set rst = db.OpenRecordset("---", _
dbOpenDynaset)

Set objXlApp = New Excel.Application
strPfad = "C:\Documents and Settings---"
ChDir strPfad
str = Dir$(strPfad & "*.xls")
Do While Len(str) > 0
str = strPfad & str
objXlApp.Workbooks.Open str, updatelinks:=0
For Each objTabelle In _
objXlApp.ActiveWorkbook.Worksheets
For Each rngZelle In objTabelle.UsedRange.Cells
If rngZelle.Value <> "" Or rngZelle.Value > 0 _
Then
rst.AddNew
rst!Datei_N = objXlApp.ActiveWorkbook.Name
rst!Tabellen_N = objTabelle.Name
rst!Zellen_A = rngZelle.Address
If rngZelle.HasFormula = True Then
rst!Wert = rngZelle.FormulaLocal
Else
rst.Update
End If
Next rngZelle
Next objTabelle
objXlApp.ActiveWorkbook.Close
str = Dir
Loop
db.Close

Set db = Nothing
Set objXlApp = Nothing
End Sub


Hoffe das Thema passt hier rein.

Grüße Holmes

immelmann
12.01.2011, 15:01
ist der Verweis auf die Excel Objektbibliothek gesetzt?

Holmes34
13.01.2011, 15:56
Aaa das hab ich ganz vergessen gehabt, kann es aber momentan sowieso nicht Testen da er mir trotzdem einen Fehler bringt scheine die Stelle:

Set rst = db.OpenRecordset("---", _
dbOpenDynaset)

Flasch zu definieren, steh grad total auf dem Schlauch :S

Jochen89
11.05.2012, 16:07
Hallo Leute
Ich komme da bei einem ählichem Problem nicht mehr weiter.

Ich habe ein Office 2007 Packet.

Und ich möchte nun von Excel per VBA in meine Access07 DB schreiben.

Mein Code sieht folgendermassen aus:
Sub test()

Dim db As DAO.Database

Set db = OpenDatabase("\\SERVER1\Folder\Jochen\8\Access\JPX.accdb")

db.Execute "INSERT INTO Test (Feld1,Feld2) VALUES ('Test', 123)"

End Sub

Nun kommt aber die Fehlermeldung: "Unbekanntes Datenbankformat"

Ich weiss, es gibt irgendwelche Schwierigkeiten mit DAO und Access2007, aber wie kann man das sonst lösen?

Danke für die Hilfe!!!!

Jochen

Josef P.
13.05.2012, 16:49
Hallo!

Wenn du eine accdb-Datei verwendest darfst du nicht dao 3.6 verwenden, sondern musst den Verweis auf ACE-DAO (Microsoft Office 12/14 Access Database Engine) setzen.

Anm.: in Access kann man auch auf DAO 3.6 verweisen, da Access 12/14 intern bereits ACE verwendet und somit auch eine ACE-Datenbank öffnen kann.

mfg
Josef