| Author |
Message |
|
Gary Coomb
#1 / 18
|
 MS Access DB - Copy Table A from Database A to Table A Database B I need to update my validation tables in a stand alone application. I want to open an Access database in Diskette Drive A and copy the Tables to the same existing Table structure existing in the application directory on the Hard Drive. What is the best way to accomplish this? Can it be done in SQL? Do I need
to simply iterate through each table record by record and transfer that way?
Sure would like it if there was a series of simple SQL statements that would
work across two different databases.
Ideas anyone???
Thanks,
Gary in Atlanta
|
| Tue, 18 Dec 2001 03:00:00 GMT |
|
 |
|
Coat
#2 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B Use SQL for this: INSERT INTO TableA FROM TableB IN "A:\mydatabase.mdb" HTH
--
Alberto Borbolla
Microsoft VB MVP
Tecnologia en Sistemas Mexico
Quote: > I need to update my validation tables in a stand alone application. I want > to open an Access database in Diskette Drive A and copy the Tables to the > same existing Table structure existing in the application directory on the > Hard Drive. > What is the best way to accomplish this? Can it be done in SQL? Do I
need
> to simply iterate through each table record by record and transfer that
way?
> Sure would like it if there was a series of simple SQL statements that
would
> work across two different databases.
> Ideas anyone???
> Thanks,
> Gary in Atlanta
|
| Tue, 18 Dec 2001 03:00:00 GMT |
|
|
|
Gary Coomb
#3 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B You can't do this using intrinsic data controls, can you?
Quote: > Use SQL for this: INSERT INTO TableA FROM TableB IN "A:\mydatabase.mdb" > HTH
> --
> Alberto Borbolla
> Microsoft VB MVP
> Tecnologia en Sistemas Mexico
> > I need to update my validation tables in a stand alone application. I
> want
> > to open an Access database in Diskette Drive A and copy the Tables to
the
> > same existing Table structure existing in the application directory on
the
> > Hard Drive.
> > What is the best way to accomplish this? Can it be done in SQL? Do I
> need
> > to simply iterate through each table record by record and transfer that
> way?
> > Sure would like it if there was a series of simple SQL statements that
> would
> > work across two different databases.
> > Ideas anyone???
> > Thanks,
> > Gary in Atlanta
|
| Tue, 18 Dec 2001 03:00:00 GMT |
|
|
|
LPP
#4 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B You allways can do something like this: Dim MyDb As Access.Application
Set MyDb = New Access.Application
MyDb.OpenCurrentDatabase ("C:\Program Files\DevStudio\VB\biblio.mdb")
MyDb.DoCmd.TransferDatabase acImport, "Microsoft Access",
"D:\MyProjects\BitNew\allbit.mdb", acTable, "tblClientes", "tblClientes", 0
MyDb.CloseCurrentDatabase
Set MyDb = Nothing
Cant you.
L.P.P.
|
| Thu, 20 Dec 2001 03:00:00 GMT |
|
|
|
Gary Coomb
#5 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B I've searched the documentation and find nothing on "TransferDatabase ". Could you elaborate? Thanks,
Gary
Quote: > You allways can do something like this: > Dim MyDb As Access.Application
> Set MyDb = New Access.Application
> MyDb.OpenCurrentDatabase ("C:\Program Files\DevStudio\VB\biblio.mdb")
> MyDb.DoCmd.TransferDatabase acImport, "Microsoft Access",
> "D:\MyProjects\BitNew\allbit.mdb", acTable, "tblClientes", "tblClientes",
0
> MyDb.CloseCurrentDatabase
> Set MyDb = Nothing
> Cant you.
> L.P.P.
|
| Thu, 20 Dec 2001 03:00:00 GMT |
|
|
|
Jim in Clevelan
#6 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B Quote:
> I've searched the documentation and find nothing on "TransferDatabase ".
> Could you elaborate?
> > You allways can do something like this:
> > Dim MyDb As Access.Application
> > Set MyDb = New Access.Application
> > MyDb.OpenCurrentDatabase ("C:\Program Files\DevStudio\VB\biblio.mdb")
> > MyDb.DoCmd.TransferDatabase acImport, "Microsoft Access",
> > "D:\MyProjects\BitNew\allbit.mdb", acTable, "tblClientes", "tblClientes",
DoCmd.TransferDatabase is from VBA within the Access application - it's not available in Visual Basic. If I recall, your original post mentioned your are working on a "stand alone" app. I believe LPP's suggestion will work ONLY IF the user's machine has MS-Access installed. If you don't expect that to be the case, there's no use pursuing this particular solution. -- Jim in Cleveland If you're writing to me, in my address change "REAL_Address.see.below" to "worldnet.att.net" "What's so funny 'bout peace, love & understanding?"
- Nick Lowe
|
| Thu, 20 Dec 2001 03:00:00 GMT |
|
|
|
William R Highfie
#7 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B On Sun, 4 Jul 1999 08:34:23 -0400, "Gary Coombs"
microsoft.public.vb.database: Quote: >I've searched the documentation and find nothing on "TransferDatabase ". >Could you elaborate? >Thanks,
>Gary
<snip> TransferDatabase is an Access thing. There is no docs in VB about it.
Alberto's post is probably a good idea.
Hope this helps
|
| Thu, 20 Dec 2001 03:00:00 GMT |
|
|
|
Gary Coomb
#8 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B
Quote:
> > I've searched the documentation and find nothing on "TransferDatabase ".
> > Could you elaborate?
> > > You allways can do something like this:
> > > Dim MyDb As Access.Application
> > > Set MyDb = New Access.Application
> > > MyDb.OpenCurrentDatabase ("C:\Program Files\DevStudio\VB\biblio.mdb")
> > > MyDb.DoCmd.TransferDatabase acImport, "Microsoft Access",
> > > "D:\MyProjects\BitNew\allbit.mdb", acTable, "tblClientes",
"tblClientes",
> DoCmd.TransferDatabase is from VBA within the Access application - it's
> not available in Visual Basic. If I recall, your original post
> mentioned your are working on a "stand alone" app. I believe LPP's
> suggestion will work ONLY IF the user's machine has MS-Access
> installed. If you don't expect that to be the case, there's no use
> pursuing this particular solution.
> --
> Jim in Cleveland
Thanks Jim, I was beginning to wonder if I was missing something. There isn't a very high probability that our users will have MS Access installed on their machines. So, in that instance, do you think there is any other solution other than opening the two databases and then iterating through each table a record at a time, transferring data a field at a time? Thanks to everyone!
Gary in Atlanta
|
| Thu, 20 Dec 2001 03:00:00 GMT |
|
|
|
Jim in Clevelan
#9 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B Quote:
> There
> isn't a very high probability that our users will have MS Access installed
> on their machines. So, in that instance, do you think there is any other
> solution other than opening the two databases and then iterating through
> each table a record at a time, transferring data a field at a time?
I think "Coatl" (aka Alberto) gave you an "INSERT INTO" SQL statament to insert the new records into your table with one step - he was the first to reply to your question. I don't think he mentioned a preliminary step - assuming your present table on the target machine should be emptied of the old info before the new stuff is inserted, you need to execute the SQL: DELETE FROM table_name Of course, if you're not deleting *all* the records, add a suitable WHERE clause to specify which records get deleted. These 2 SQL statements will work faster, with less code, than your idea about iterating through each record in the table - try a test both ways and see how much the time differs - if table is relatively small, the diff won't be too noticeable, but if ya got 1,000 records, your users will appreciate the faster approach. -- Jim in Cleveland If you're writing to me, in my address change "REAL_Address.see.below" to "worldnet.att.net"
|
| Thu, 20 Dec 2001 03:00:00 GMT |
|
|
|
Coat
#10 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B Maybe I'm missing something, but where the data controls fit in this problem? Ok, you need to open the database yourself:
Dim db as database
dim strSQL as string
set db=dbengine(0).opendatabase ("target database.mdb")
strSQL="INSERT INTO ...
db.Execute (strSQL)
--
Alberto Borbolla
Microsoft VB MVP
Tecnologia en Sistemas Mexico
Quote: > You can't do this using intrinsic data controls, can you?
> > Use SQL for this: INSERT INTO TableA FROM TableB IN "A:\mydatabase.mdb"
> > HTH
> > --
> > Alberto Borbolla
> > Microsoft VB MVP
> > Tecnologia en Sistemas Mexico
> > > I need to update my validation tables in a stand alone application. I
> > want
> > > to open an Access database in Diskette Drive A and copy the Tables to
> the
> > > same existing Table structure existing in the application directory on
> the
> > > Hard Drive.
> > > What is the best way to accomplish this? Can it be done in SQL? Do I
> > need
> > > to simply iterate through each table record by record and transfer
that
> > way?
> > > Sure would like it if there was a series of simple SQL statements that
> > would
> > > work across two different databases.
> > > Ideas anyone???
> > > Thanks,
> > > Gary in Atlanta
|
| Thu, 20 Dec 2001 03:00:00 GMT |
|
|
|
LPP
#11 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B Sorry for the confusion, but I assumed you were working with VB and Access. Once again Sorry..... L.P.P. Lus Prudente Buy the way you can get TransferDatabase if you reference in VB the Microsoft Access 8.0 Object Library. L.P.P.
Quote: > On Sun, 4 Jul 1999 08:34:23 -0400, "Gary Coombs"
> microsoft.public.vb.database: > >I've searched the documentation and find nothing on "TransferDatabase ".
> >Could you elaborate?
> >Thanks,
> >Gary
> <snip>
> TransferDatabase is an Access thing. There is no docs in VB about it.
> Alberto's post is probably a good idea.
> Hope this helps
|
| Fri, 21 Dec 2001 03:00:00 GMT |
|
|
|
Gary Coomb
#12 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B OK, no matter what I do I get a syntax error. Here is the code I've put in my cmdOK sub... Private Sub cmdOK_Click()
Dim dbsUpdateTable As Database
Dim dbsSmartII As Database
Dim strSQL As String
Set dbsUpdateTable = OpenDatabase(App.Path & "\UpdateTables.mdb")
Set dbsSmartII = OpenDatabase(App.Path & "\SmartII.mdb")
strSQL = "DELETE FROM AUDIOQUA"
dbsSmartII.Execute (strSQL)
strSQL = "INSERT INTO AUDIOQUA FROM AUDIOQUA IN
c:\datatest\UpdateTables.mdb"
dbsSmartII.Execute (strSQL)
End Sub
The "DELETE FROM AUDIOQUA" works fine.
The next strSQL fails.
I've tried about everything I can to reference the "UpdateTables.mdb"
database and I continue to get syntax errors. I've tried...
strSQL = "INSERT INTO AUDIOQUA FROM AUDIOQUA IN
'c:\datatest\UpdateTables.mdb'"
and same error. What am I doing wrong?
Quote: > Maybe I'm missing something, but where the data controls fit in this > problem? > Ok, you need to open the database yourself:
> Dim db as database
> dim strSQL as string
> set db=dbengine(0).opendatabase ("target database.mdb")
> strSQL="INSERT INTO ...
> db.Execute (strSQL)
> --
> Alberto Borbolla
> Microsoft VB MVP
> Tecnologia en Sistemas Mexico
> > You can't do this using intrinsic data controls, can you?
> > > Use SQL for this: INSERT INTO TableA FROM TableB IN
"A:\mydatabase.mdb"
> > > HTH
> > > --
> > > Alberto Borbolla
> > > Microsoft VB MVP
> > > Tecnologia en Sistemas Mexico
> > > > I need to update my validation tables in a stand alone application.
I
> > > want
> > > > to open an Access database in Diskette Drive A and copy the Tables
to
> > the
> > > > same existing Table structure existing in the application directory
on
> > the
> > > > Hard Drive.
> > > > What is the best way to accomplish this? Can it be done in SQL? Do
I
> > > need
> > > > to simply iterate through each table record by record and transfer
> that
> > > way?
> > > > Sure would like it if there was a series of simple SQL statements
that
> > > would
> > > > work across two different databases.
> > > > Ideas anyone???
> > > > Thanks,
> > > > Gary in Atlanta
|
| Fri, 21 Dec 2001 03:00:00 GMT |
|
|
|
Jim in Clevelan
#13 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B Quote:
> OK, no matter what I do I get a syntax error. Here is the code I've put in
> my cmdOK sub...
> Private Sub cmdOK_Click()
> Dim dbsUpdateTable As Database
> Dim dbsSmartII As Database
> Dim strSQL As String
> Set dbsUpdateTable = OpenDatabase(App.Path & "\UpdateTables.mdb")
> Set dbsSmartII = OpenDatabase(App.Path & "\SmartII.mdb")
> strSQL = "DELETE FROM AUDIOQUA"
> dbsSmartII.Execute (strSQL)
> strSQL = "INSERT INTO AUDIOQUA FROM AUDIOQUA IN
> c:\datatest\UpdateTables.mdb"
<snip> Gary, Glad my part (the DELETE) worked so easily! For the INSERT, since you're working with two databases, try making your SQL more explicit: "INSERT INTO SmartII.AUDIOQUA FROM UpdateTables.AUDIOQUA IN...
Hope that'll do it.
--
Jim in Cleveland
If you're writing to me, in my address
change "REAL_Address.see.below" to "worldnet.att.net"
|
| Fri, 21 Dec 2001 03:00:00 GMT |
|
|
|
Coat
#14 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B Sorry, this is the problem of writing code on the fly, the proper syntax is: strSQL = "INSERT INTO AUDIOQUA IN 'c:\datatest\smartII.mdb' SELECT * FROM
AUDIOQUA"
So in this case, you have to execute the SQL against the second mdb
dbsUpdateTable (strSQL)
--
Alberto Borbolla
Microsoft VB MVP
Tecnologia en Sistemas Mexico
Quote: > OK, no matter what I do I get a syntax error. Here is the code I've put in > my cmdOK sub... > Private Sub cmdOK_Click()
> Dim dbsUpdateTable As Database
> Dim dbsSmartII As Database
> Dim strSQL As String
> Set dbsUpdateTable = OpenDatabase(App.Path & "\UpdateTables.mdb")
> Set dbsSmartII = OpenDatabase(App.Path & "\SmartII.mdb")
> strSQL = "DELETE FROM AUDIOQUA"
> dbsSmartII.Execute (strSQL)
> strSQL = "INSERT INTO AUDIOQUA FROM AUDIOQUA IN
> c:\datatest\UpdateTables.mdb"
> dbsSmartII.Execute (strSQL)
> End Sub
> The "DELETE FROM AUDIOQUA" works fine.
> The next strSQL fails.
> I've tried about everything I can to reference the "UpdateTables.mdb"
> database and I continue to get syntax errors. I've tried...
> strSQL = "INSERT INTO AUDIOQUA FROM AUDIOQUA IN
> 'c:\datatest\UpdateTables.mdb'"
> and same error. What am I doing wrong?
> > Maybe I'm missing something, but where the data controls fit in this
> > problem?
> > Ok, you need to open the database yourself:
> > Dim db as database
> > dim strSQL as string
> > set db=dbengine(0).opendatabase ("target database.mdb")
> > strSQL="INSERT INTO ...
> > db.Execute (strSQL)
> > --
> > Alberto Borbolla
> > Microsoft VB MVP
> > Tecnologia en Sistemas Mexico
> > > You can't do this using intrinsic data controls, can you?
> > > > Use SQL for this: INSERT INTO TableA FROM TableB IN
> "A:\mydatabase.mdb"
> > > > HTH
> > > > --
> > > > Alberto Borbolla
> > > > Microsoft VB MVP
> > > > Tecnologia en Sistemas Mexico
> > > > > I need to update my validation tables in a stand alone
application.
> I
> > > > want
> > > > > to open an Access database in Diskette Drive A and copy the Tables
> to
> > > the
> > > > > same existing Table structure existing in the application
directory
> on
> > > the
> > > > > Hard Drive.
> > > > > What is the best way to accomplish this? Can it be done in SQL?
Do
> I
> > > > need
> > > > > to simply iterate through each table record by record and transfer
> > that
> > > > way?
> > > > > Sure would like it if there was a series of simple SQL statements
> that
> > > > would
> > > > > work across two different databases.
> > > > > Ideas anyone???
> > > > > Thanks,
> > > > > Gary in Atlanta
|
| Fri, 21 Dec 2001 03:00:00 GMT |
|
|
|
Gary Coomb
#15 / 18
|
MS Access DB - Copy Table A from Database A to Table A Database B Alberto, What do you use and recommend for a SQL reference?
Thanks again for your expertise!
Gary in Atlanta
Quote: > Sorry, this is the problem of writing code on the fly, the proper syntax is: > strSQL = "INSERT INTO AUDIOQUA IN 'c:\datatest\smartII.mdb' SELECT * FROM
> AUDIOQUA"
> So in this case, you have to execute the SQL against the second mdb
> dbsUpdateTable (strSQL)
> --
> Alberto Borbolla
> Microsoft VB MVP
> Tecnologia en Sistemas Mexico
|
| Fri, 21 Dec 2001 03:00:00 GMT |
|
| |
