DAO - Insert Records 2

[LeonTang]

Hi,

I am trying to copy records from 1 database to another. These 2 databases(MS Access) are similar (in the sense of structure i.e. same tables, columns). I am supposed to use DAO to complete this.

I would like to know if it is possible to copy the records from 1 table to another without having to loop through the entire table? Something like:-

insert into table1.userTable select * from table2.userTable

I have seen the insert sql syntax in vb help and there is this 'IN' keyword. Does it do what I want? And also, how can I loop through all the tables in my database without hardcoding the name?

Are there any bulkloading functions in Access too?

Thanks for your time,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best

 

[HermanvLimbeek]

You can use a Select ... Into statement to execute a make-table query (create a copy of the table in another database). Following example comes from Visual Basic Help:

Sub SelectIntoX()

Dim dbs As Database
Dim qdf As QueryDef

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb&quot

' Select all records in the Employees table
' and copy them into a new table, Emp Backup.
dbs.Execute "SELECT Employees.* INTO " _
& "[Emp Backup] FROM Employees;"

' Delete the table because this is a demonstration.
dbs.Execute "DROP TABLE [Emp Backup];"

dbs.Close

End Sub

Use the Insert ... Into statement to add data to an existing table.
Other sample from the Help:
Sub InsertIntoX1()

Dim dbs As Database

' Modify this line to include the path to Northwind
' on your computer.
Set dbs = OpenDatabase("Northwind.mdb&quot

' Select all records in the New Customers table
' and add them to the Customers table.
dbs.Execute " INSERT INTO Customers " _
& "SELECT * " _
& "FROM [New Customers];"

dbs.Close

End Sub

Now the question about looping through tablenames I'll post a FAQ which shows how to do it.

The answer to your last question depends on where do you want to load data from?

Herman :-Q

 

[JhongJ]

it is possible to use the syntax in VB and in ACCESS :

Insert into table1.userTable select * from table2.userTable

 

[LeonTang]

Sorry, I made a mistake.

What I wanted was insert data from a table in 1 database to a similar table in another database and these 2 tables have the same structure. Something like:-

insert into database1.userTable from database2.userTable

I know the sql syntax is not correct but its just an example to show what I want..

Thanks again,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best

 

[HermanvLimbeek]

Hi Leon,

Just a few steps: Open your database, create a SQL statement, execute it on your opened database and the data will be copied (real fast).

set MyDB = MyWorkspace.OpenDatabase (MyFile, True, False)
SQL = "INSERT INTO YourTable IN " & Chr(34) & YourExportFile & Chr(34) & " SELECT * FROM YourTable"
MyDB.Execute SQL

That's All!

Herman :-Q

 

[LeonTang]

Hi Herman,

Thanks for your reply. I have seen your faq and it seems alright. I have yet tested it out as the program is at my work place. I will inform you once I have tested it out.

Anyway, can you explain what does 'Chr(34)' and 'YourExportFile' represent? Does YourExportFile represent the path of a database?

And for bulkloading, can you explain more as what methods of bulkloading is possible with Access database and what is the best method?

Thanks again,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best

 

[HermanvLimbeek]

Chr(34) is the " character. While making a SQL string you cannot use the " because VB will interpret it as the end of the string. But since the YourExportFile variable represents the path to and the name of the database (C:\Access\Database\ExportDB), you'll have to place the string between quotes to make a correct executable SQL string.

Herman :-Q

 

[LeonTang]

Hi Herman,

Thanks for your explanation. I have tested out the examples and they work. Thanks a lot for all your help.

I have a few last questions:

1) I realised that my tables actually begins from index 9 onwards. Meaning mydb.TableDefs(9).name Why is this so? What are the earlier tables used for?

2) What kind of methods of bulkloading is available in access?

Best Regards,
Leon If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best

 

[HermanvLimbeek]

The first tables will be system tables. These are normally hidden.

I used the RecordSet's GetRows method in ADO to load bulk data from a ODBC source into a variant array. I then scanned the array and filled the appropriate tables with data. The GetRows method is also available in DAO. I suppose it works in a similar way, but I've no experience with this method in DAO.
There's also a datapump facility, freeware I believe. I'll take a look in my archive and see if I can find some information about it.

Herman :-Q

 

[LeonTang]

Thanks a lot Herman If you need additional help, you can email to me at zaoliang@hotmail.com I don't guaranty that I will be able to solve your problems but I will try my best