Put records from one table to another

[sowhatnow]

I need an answer today if possible! I have a dBase file (apimport.dbf) attached to deChase data environment with connection called cn_dBase. That table is called apimport. I have another connection called cn_SQL with a table called tblRecords. That is connected to the SQL server 7.0.

Using Visual Basic, I need to insert all the records from apimport into tblRecords. My team have tried alot of stuff and we can't figure out how to do it. This is an application which my users will use. Please Help! Thank you so much.

 

[jlgdeveloper]

In sql server, you can Import the data. Right click the db/table in sql server, choose import. An import wizard will guide you through the process. You might need a dsn pointing at the dbf.

Jonathan Galpin MCSD

http://www.iqzero.net

because software should be easy to use

 

[sowhatnow]

Thank you but we don't want to use the DTS package and the import wizards. We tried to program that capibilities but no luck. This is why we are doing the data environment method. the network guys changed SQL server stuff so we can't use BCP. I tried the insert statment but I didn't know how to use it with data environment. Please Help!
Thank you.

 

[jlgdeveloper]

Karen

The usual method I employ in this scenario is to write a one time vb script to open a recordset on db1 on machine 1(read), open a write recordset on machine2, and stuff the data from one to the other.

There are twenty ways to skin this cat. Look at the faq section. Here is one I wrote: faq222-2103



Jonathan Galpin MCSD

http://www.iqzero.net

because software should be easy to use

 

[wayne1954]

funny i had a simliar situation today...

i used an sql statement in vb:

strSQL="SELECT * _
INTO [DEVEL2000 20030716140205R] _
IN 'c:\program files\prospectconnect\pct.mdb' _
FROM [crApp2-1] INNER JOIN [crApp2-2] ON [crApp2-1].id = [crApp2-2].crApp1ID _
WHERE [crApp2-1].id= 11;"

dbsLocal.execute strSQL

[DEVEL2000 20030716140205R] is the destination table
'c:\program files\prospectconnect\pct.mdb' is the external database file containing the destination table

wayne

 

[sowhatnow]

Thank you everyone for your response. Wayne1954, I saw an example on what you gave me. I understood it if the data resides in .mdb. How on earth would I modify it to grab the data from a .dbf file and the correct syntax?

strSQL = "select * into [tblRecords] in _
'\\ServerA\ExtractFolder\Chase\apimport.dbf' _
from ?????

If this question could be answered, alot of doors would be opened for our group.

jlgdeveloper, Thank you for FA!222-2103. Unfortunately, we are unable to use it. The one thing our group stresses quite vigorously is simplicity. Because of the multiple platform of computers in our business and the multiple level of computer experience amoung our users, network, and etc., we have to program everything simple.