Printable View
This appears simple so forgive my ignorance here.
I am creating recordsets and watching for err.# = 3078, missing table. if i see this error, i would like to copy the entire table from another database into my first database. is there a command to do this from vb6?
Thank you very much!
E
INSERT INTO Table1 IN 'ExternalDatabasePath1' SELECT *
FROM Table2 IN 'ExternalDatabasePath2';
where externaldatabasepath1 & 2 are something like c:\databases\db1.mdb & c:\databases\db2.mdb
(INSERT INTO Table1 SELECT * FROM Table2 IN 'c:/aaa/bbb/Database.mdb')
if this table does not exist you can create table before inserting into it.
Iouri Boutchkine
[email protected]
Thank you Iouri. I now at a loss as to how to create the table. The original intent was to check for the missing table then simply insert it into the user's database.
below is some of my code:
'first sub
Set db = DBEngine.Workspaces(0).OpenDatabase(GetDataFile)
Err.Clear
Set rsInfo = .OpenRecordset(AP_rsInfo, dbOpenDynaset)
If Err.Number = 3078 Then
'add table
GetMissingTable "Informantion"
'in sub GetMissingTable(tbl as string):
Set mdb = DBEngine.Workspaces(0).OpenDatabase(GetDataBkupFile)
'the functions return the full file name
intent now is to copy the entire "information" table from the backup file into the other .mdb
i see a "createtabledefs" cmd but i am not sure this is what i need? would i need to know all fields and associated information - to create the table?
Thank you for your patience.
E
Then what you need to use is the SELECT INTO SQL command. Look up the INTO clause in MSDN.
Here's my example:
'Check the table definitions of the Clearance database to
'see if a table named 'ClearanceBackup_[today's date]'
'already exists, if it does then delete it.
for i = 0 to dbClearance.TableDefs.Count - 1
set TDObj = dbClearance.TableDefs(i)
If TDObj.Name = "ClearanceBackup" & strDeletedDate then
dbClearance.TableDefs.Delete ("ClearanceBackup" & _
strDeletedDate)
Exit for
End If
next
'Create a backup table (this is a 'make-table query')
SQLStmt = "SELECT CLEARANCE.* INTO [ClearanceBackup" & strDeletedDate & _
"] FROM CLEARANCE"
dbClearance.Execute (SQLStmt)
Using ADO
Don't forget to add references to ADO and ADOX
Dim tbl As ADOX.Table
Set tbl = New ADOX.Table
tbl.Name = "tblCustomer"
cat.Tables.Append tbl
'=========
Dim col As ADOX.Column
Set col = New ADOX.Column' Create first name field
With col
.Name = "FirstName"
.DefinedSize = 50
End With
' Add the new column to the table.tbl.Columns.Append col
Set col = New ADOX.Column
' Create last name field.
With col
.Name = "LastName"
.DefinedSize = 50
End With
' Add the new column to the table.
tbl.Columns.Append col
'or--------shortcut to add column------
tbl.Columns.Append "FirstName", _
adVarWChar, 50
tbl.Columns.Append "LastName", _
adVarWChar, 50
'------------
Iouri Boutchkine
[email protected]
this looks like a simple solution and i would like to implement it. however i get an error message which i hope is obvious to you? this is my code:
SQLStmt = "SELECT " & tbl & ".* INTO [" & _
tbl & " IN " & GetAPDataFile & "] FROM " & tbl
On Error Resume Next
mdb.Execute (SQLStmt)
MsgBox Err.Description
"The database engine can't find '(tblname) IN (path\filename)'. Make sure it is a valid parameter or alias name, that doesn't include invalid characters or punctuation, and that the name isn't too long."
I don't understand the error message since my understanding is this is a make table query? it should not 'find' the table it should 'make' the table?
thank you :)
You've misplaced the bracket characters. My example is probably confusing because of all the string concatenation and other crap in it. Let's see if I can give you a cleaner example:
'If SourceTable and TargetTable are the ACTUAL table names then this is what you would have.
'If you use variables in their place then you have to place all that string concatenation junk in it, hence,
'all my ampersand symbols and underscores from the previous example
SQLStmt = "SELECT SourceTable.* INTO [TargetTable] FROM SourceTable"
Now, this example assumes that both tables are in the same .mdb file and I make sure that if the target table already exists that I delete it first. I don't know, that situation may not be appropriate for you, for me it is.
Iouri, thank you for this possible solution. using it would require knowledge of the fields and their defined size would it not? i would like to get demmith's proferred solution to work. i will keep this information as a solution to another challenge. thank you!
My target table is in another .mdb file so i moved the bracket. when i kept the bracket around the table i got a different error message:
The SELECT statement includes a reserved word or an argument name that is misspelled or missing, or the punctuation is incorrect.
an error message that is clearly indicative of the problem?
I FOUND MY ERROR! I forgot to place the path\filename in single quotes!!
thank you very much, it now works :)!!
Glad I could help. I already had an ordeal this morning with funky table names and using single backquotes. I spend more time searching for good explanations and good examples than anything else. Ain't it grand?! :-)
I like this code. I am already out of vote for today (holes in my hands, maybe...), so wait until tomorrow, Iouri...
Special thanks to Lothar "the Great" Haensler. Come back soon, you Guru.
As promised, my vote for you.
Special thanks to Lothar "the Great" Haensler. Come back soon, you Guru.