PDA

Vollständige Version anzeigen : Tabelle kopieren...?


Andi_D
23.10.2001, 14:50
Hallo an alle,

weiß jemand, wie ich von einer dritten Anwendung aus (Anwendung mit VB 5.0 geschrieben) eine Tabelle von einer Access-DB in eine andere Access-DB kopieren kann...?

Danke im voraus...

Gruz Andi...

Manuela Kulpa
23.10.2001, 16:37
<font face="Verdana" size="2">Hallo Andi!

Anbei ein kleines Beispiel! Wenn du die Indexes und andere Eigenschaften mit kopieren möchtest, müsstest du dich noch mal bei mir melden.

vg</font>

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Private Sub</span> Command1_Click()

<span class="TOKEN">Dim</span> dbsDB <span class="TOKEN">As</span> DAO.Database

<span class="TOKEN">Dim</span> sPath <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> sInDB <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> sDesDB <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> sTable <span class="TOKEN">As</span> <span class="TOKEN">String</span>

sPath = "E:\Developer Help\Access\Nordwind (e)\"
sInDB = "nwind.mdb"
sDesDB = "nwind1.mdb"
sTable = "Categories"

<span class="TOKEN">Set</span> dbsDB = DAO.DBEngine.OpenDatabase(sPath & sInDB)

<span class="REM"> ' Beispielaufruf</span>
CopyTableSimple dbsDB, sTable, sPath & sDesDB

<span class="TOKEN">If</span> <span class="TOKEN">Not</span> dbsDB <span class="TOKEN">Is</span> <span class="TOKEN">Nothing</span> <span class="TOKEN">Then</span> dbsDB.Close: <span class="TOKEN">Set</span> dbsDB = <span class="TOKEN">Nothing</span>

<span class="TOKEN">End</span> <span class="TOKEN">Sub</span><hr>
<span class="TOKEN">Public Sub</span> CopyTableSimple(dbsIn <span class="TOKEN">As</span> DAO.Database, _
sTable <span class="TOKEN">As</span> String, _
sDestination <span class="TOKEN">As</span> <span class="TOKEN">String</span>)

<span class="REM"> '//////////////////////////////////////////////////////////////////////</span>
<span class="REM"> ' Kommentar: Kopiert die angegebene Tabelle von einer DB zur anderen</span>
<span class="REM"> ' Diese einfache Variante kopiert nur die Feldnamen, die</span>
<span class="REM"> ' Datentypen und den Inhalt der Tabelle</span>
<span class="REM"> ' Indexes und andere Eigenschaften werden nicht kopiert</span>
<span class="REM"> ' Parameters: dbsIn - ein Zeiger auf die Datenbank</span>
<span class="REM"> ' sTable - Name der Tabelle, die kopiert werden soll</span>
<span class="REM"> ' sDestination - Pfad und Name der Zieldatenbank</span>
<span class="REM"> '//////////////////////////////////////////////////////////////////////</span>

<span class="TOKEN">Dim</span> sSQL <span class="TOKEN">As</span> <span class="TOKEN">String</span>

<span class="REM"> ' eine Fehlerbehandlung kann nie schaden</span>
<span class="TOKEN">On Error GoTo</span> HandleErr

<span class="REM"> ' SQL-String zusammenbasteln</span>
sSQL = "SELECT [" & sTable & _
"].* INTO [" & sTable & "] "

sSQL = sSQL & "IN '" & sDestination & _
"' FROM [" & sTable & "];"

<span class="REM"> ' Abfrage ausführen</span>
dbsIn.Execute sSQL

ExitHere:
<span class="TOKEN">Exit Sub</span>

HandleErr:
Select Case Err.Number
<span class="TOKEN">Case Else</span>
MsgBox "Fehler " & Err.Number & ": " & Err.Description, vbCritical, "basDiverses.CopyTableSimple"
<span class="TOKEN">End</span> Select
<span class="TOKEN">Resume</span> ExitHere
<span class="TOKEN">End</span> <span class="TOKEN">Sub</span></pre></div>

Andi_D
23.10.2001, 18:43
Hi Manuela,

erstma besten Dank füa die schnelle Antwort. Funzt alles hervorzüglich...
Wennste dann noch`n klaane Tip hättst um die Eigenschaften zu übernehmen, wär` des escht supaspitzenklasse....

Gruz Andi...

Manuela Kulpa
25.10.2001, 05:50
<font face="Verdana" size="2">Hallo Andi!

Anbei die erweiterte Fassung :)

Viel Spaß damit</font>

<div><link href="http://www.ms-office-forum.net/forum/externals/codeconv.css" rel="stylesheet"><pre><span class="TOKEN">Private Sub</span> Command1_Click()

<span class="TOKEN">Dim</span> dbsDB <span class="TOKEN">As</span> DAO.Database

<span class="TOKEN">Dim</span> sPath <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> sInDB <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> sDesDB <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> sTable <span class="TOKEN">As</span> <span class="TOKEN">String</span>

sPath = "E:\Developer Help\Access\Nordwind (e)\"
sInDB = "nwind.mdb"
sDesDB = "nwind1.mdb"
sTable = "Categories"

<span class="TOKEN">Set</span> dbsDB = DAO.DBEngine.OpenDatabase(sPath & sInDB)

Me.MousePointer = vbHourglass
<span class="REM"> ' Beispielaufruf</span>
CopyTableFull dbsDB, sTable, sPath & sDesDB, <span class="TOKEN">False</span>
Me.MousePointer = vbDefault

MsgBox "Ich habe fertig!", vbInformation, "Hinweis"

<span class="TOKEN">If</span> <span class="TOKEN">Not</span> dbsDB <span class="TOKEN">Is</span> <span class="TOKEN">Nothing</span> <span class="TOKEN">Then</span> dbsDB.Close: <span class="TOKEN">Set</span> dbsDB = <span class="TOKEN">Nothing</span>

<span class="TOKEN">End</span> <span class="TOKEN">Sub</span><hr>
<span class="TOKEN">Public Sub</span> CopyTableFull( _
dbsSource <span class="TOKEN">As</span> DAO.Database, _
sTable <span class="TOKEN">As</span> String, _
sDestination <span class="TOKEN">As</span> String, _
fStructureOnly <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>)

<span class="REM"> '/////////////////////////////////////////////////////////////////////</span>
<span class="REM"> ' Kommentar: Kopiert die angegebene Tabelle von einer DB zur anderen,</span>
<span class="REM"> ' inclusive der Eigenschaften, Indexes und optional</span>
<span class="REM"> ' die Tabellendaten</span>
<span class="REM"> ' Parameters: dbsSource - ein Zeiger auf die Datenbank</span>
<span class="REM"> ' sTable - Name der Tabelle die kopiert werden soll</span>
<span class="REM"> ' sDestination - Pfad und Name der Zieldatenbank</span>
<span class="REM"> ' fStructureOnly - True, um nur die Struktur zu kopieren</span>
<span class="REM"> ' False, um sowohl die Struktur und die Daten zu kopieren</span>
<span class="REM"> '/////////////////////////////////////////////////////////////////////</span>

<span class="TOKEN">Dim</span> dbsDest <span class="TOKEN">As</span> DAO.Database
<span class="TOKEN">Dim</span> tdfSource <span class="TOKEN">As</span> DAO.TableDef
<span class="TOKEN">Dim</span> tdfDest <span class="TOKEN">As</span> DAO.TableDef
<span class="TOKEN">Dim</span> fldSource <span class="TOKEN">As</span> DAO.Field
<span class="TOKEN">Dim</span> fldDest <span class="TOKEN">As</span> DAO.Field
<span class="TOKEN">Dim</span> idxSource <span class="TOKEN">As</span> DAO.Index
<span class="TOKEN">Dim</span> idxDest <span class="TOKEN">As</span> DAO.Index
<span class="TOKEN">Dim</span> prpNew <span class="TOKEN">As</span> DAO.Property
<span class="TOKEN">Dim</span> iCounter <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> iCounter2 <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> iCounter3 <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> iSaveErr <span class="TOKEN">As</span> <span class="TOKEN">Integer</span>
<span class="TOKEN">Dim</span> sName <span class="TOKEN">As</span> <span class="TOKEN">String</span>
<span class="TOKEN">Dim</span> sSQL <span class="TOKEN">As</span> <span class="TOKEN">String</span>

<span class="TOKEN">On Error GoTo</span> HandleErr

<span class="TOKEN">Set</span> dbsDest = DBEngine.Workspaces(0).OpenDatabase(sDestination)

<span class="TOKEN">Set</span> tdfSource = dbsSource.TableDefs(sTable)
<span class="TOKEN">Set</span> tdfDest = dbsDest.CreateTableDef(tdfSource.Name)

tdfDest.Properties("Attributes") = tdfSource.Properties("Attributes")
tdfDest.Properties("SourceTableName") = tdfSource.Properties("SourceTableName")

<span class="TOKEN">For</span> iCounter = 0 <span class="TOKEN">To</span> tdfSource.Fields.Count - 1
<span class="TOKEN">Set</span> fldSource = tdfSource.Fields(iCounter)
<span class="TOKEN">Set</span> fldDest = tdfDest.CreateField(fldSource.Name, fldSource.Properties("Type"))

<span class="TOKEN">For</span> iCounter2 = 0 <span class="TOKEN">To</span> fldSource.Properties.Count - 1
sName = fldSource.Properties(iCounter2).Name

<span class="TOKEN">On</span> <span class="TOKEN">Error</span> <span class="TOKEN">Resume</span> <span class="TOKEN">Next</span>
fldDest.Properties(sName) = fldSource.Properties(sName)
iSaveErr = Err
<span class="TOKEN">On Error GoTo</span> HandleErr

Select Case iSaveErr

Case 0

Case 3219

Case 3270
<span class="TOKEN">Set</span> prpNew = fldDest.CreateProperty(sName)
prpNew.Type = fldSource.Properties(sName).Type
prpNew.Value = fldSource.Properties(sName).Value

<span class="TOKEN">On</span> <span class="TOKEN">Error</span> <span class="TOKEN">Resume</span> <span class="TOKEN">Next</span>
fldDest.Properties.Append prpNew
<span class="TOKEN">On Error GoTo</span> HandleErr

Case 3001, 3267, 3251

<span class="TOKEN">Case Else</span>
<span class="TOKEN">Error</span> iSaveErr

<span class="TOKEN">End</span> Select

<span class="TOKEN">Next</span> iCounter2

tdfDest.Fields.Append fldDest

<span class="TOKEN">Next</span> iCounter

dbsDest.TableDefs.Append tdfDest

<span class="TOKEN">For</span> iCounter = 0 <span class="TOKEN">To</span> tdfSource.Properties.Count - 1
sName = tdfSource.Properties(iCounter).Name

<span class="TOKEN">If</span> sName <> "Name" <span class="TOKEN">And</span> sName <> "OrderBy" <span class="TOKEN">Then</span>

<span class="TOKEN">On</span> <span class="TOKEN">Error</span> <span class="TOKEN">Resume</span> <span class="TOKEN">Next</span>
tdfDest.Properties(sName).Value = tdfSource.Properties(sName).Value
iSaveErr = Err
<span class="TOKEN">On Error GoTo</span> HandleErr

Select Case iSaveErr

Case 0

Case 3219

Case 3270
<span class="TOKEN">Set</span> prpNew = tdfDest.CreateProperty(sName)
prpNew.Type = tdfSource.Properties(sName).Type
prpNew.Value = tdfSource.Properties(sName).Value
tdfDest.Properties.Append prpNew

Case 3268

Case 3001, 3267, 3251

<span class="TOKEN">Case Else</span>
<span class="TOKEN">Error</span> iSaveErr

<span class="TOKEN">End</span> Select

<span class="TOKEN">End</span> <span class="TOKEN">If</span>

<span class="TOKEN">Next</span> iCounter

<span class="TOKEN">For</span> iCounter = 0 <span class="TOKEN">To</span> tdfSource.Indexes.Count - 1
<span class="TOKEN">Set</span> idxSource = tdfSource.Indexes(iCounter)
<span class="TOKEN">If</span> <span class="TOKEN">Not</span> (idxSource.Foreign) <span class="TOKEN">Then</span>
<span class="TOKEN">Set</span> idxDest = tdfSource.CreateIndex(idxSource.Name)

idxDest.Properties("Primary") = idxSource.Properties("Primary")
idxDest.Properties("Unique") = idxSource.Properties("Unique")
idxDest.Properties("Clustered") = idxSource.Properties("Clustered")
idxDest.Properties("Required") = idxSource.Properties("Required")
idxDest.Properties("IgnoreNulls") = idxSource.Properties("IgnoreNulls")

<span class="TOKEN">For</span> iCounter2 = 0 <span class="TOKEN">To</span> idxSource.Fields.Count - 1
<span class="TOKEN">Set</span> fldSource = idxSource.Fields(iCounter2)
<span class="TOKEN">Set</span> fldDest = idxDest.CreateField(fldSource.Name)

<span class="TOKEN">For</span> iCounter3 = 0 <span class="TOKEN">To</span> fldSource.Properties.Count - 1
sName = fldSource.Properties(iCounter3).Name

<span class="TOKEN">On</span> <span class="TOKEN">Error</span> <span class="TOKEN">Resume</span> <span class="TOKEN">Next</span>
fldDest.Properties(sName).Value = fldSource.Properties(sName).Value
iSaveErr = Err
<span class="TOKEN">On Error GoTo</span> HandleErr

Select Case iSaveErr

Case 0

Case 3219

Case 3270
<span class="TOKEN">Set</span> prpNew = tdfDest.CreateProperty(sName)
prpNew.Type = tdfSource.Properties(sName).Type
prpNew.Value = tdfSource.Properties(sName).Value
tdfDest.Properties.Append prpNew

Case 3001, 3267, 3251

<span class="TOKEN">Case Else</span>
<span class="TOKEN">Error</span> iSaveErr

<span class="TOKEN">End</span> Select

<span class="TOKEN">Next</span> iCounter3

idxDest.Fields.Append fldDest

<span class="TOKEN">Next</span> iCounter2

tdfDest.Indexes.Append idxDest

<span class="TOKEN">For</span> iCounter2 = 0 <span class="TOKEN">To</span> idxSource.Properties.Count - 1

sName = idxSource.Properties(iCounter2).Name

<span class="TOKEN">On</span> <span class="TOKEN">Error</span> <span class="TOKEN">Resume</span> <span class="TOKEN">Next</span>
idxDest.Properties(sName) = idxSource.Properties(sName)
iSaveErr = Err
<span class="TOKEN">On Error GoTo</span> HandleErr

Select Case iSaveErr

Case 0

Case 3219

Case 3268

Case 3270
<span class="TOKEN">Set</span> prpNew = idxDest.CreateProperty(sName)
prpNew.Type = idxSource.Properties(sName).Type
prpNew.Value = idxSource.Properties(sName).Value
idxDest.Properties.Append prpNew

Case 3001, 3267, 3251

<span class="TOKEN">Case Else</span>
<span class="TOKEN">Error</span> iSaveErr

<span class="TOKEN">End</span> Select

<span class="TOKEN">Next</span> iCounter2

<span class="TOKEN">End</span> <span class="TOKEN">If</span>

<span class="TOKEN">Next</span> iCounter

<span class="TOKEN">If</span> <span class="TOKEN">Not</span> fStructureOnly <span class="TOKEN">Then</span>
sSQL = "INSERT INTO [" & sTable & "] IN '" & sDestination & "' "
sSQL = sSQL & "SELECT [" & sTable & "].* "
sSQL = sSQL & "FROM [" & sTable & "];"
dbsSource.Execute sSQL
<span class="TOKEN">End</span> <span class="TOKEN">If</span>

ExitHere:
<span class="TOKEN">Exit Sub</span>

HandleErr:
Select Case Err.Number
<span class="TOKEN">Case Else</span>
MsgBox "Fehler " & Err.Number & ": " & Err.Description, vbCritical, "basDiverses.CopyTableFull"
<span class="TOKEN">End</span> Select
<span class="TOKEN">Resume</span> ExitHere

<span class="TOKEN">End</span> <span class="TOKEN">Sub</span></pre></div>