Working with Access2000 1

[spayne]

Using ADO with VFP6, does anyone know how to append data from myTable1 in an Access2K .mdb to myTable2 in the same .mdb? The data currently residing in myTable2 has to remain intact.

Thanks,
Steve

 

[jonscott8]

Presuming oConnection represents a valid open ADO connection object, and both tables have the same structure:

lnAffected=0
lcSQL="INSERT INTO Table2 SELECT * FROM Table1"
oConnection.Execute(lcSQL,@lnAffected)
IF lnAffected > 0
WAIT WIND "Data Transferred: "+TRAN(lnAffected)+" Records"
ENDIF Jon Hawkins

The World Is Headed For Mutiny,
When All We Want Is Unity. - Creed

 

[spayne]

Jon,

I tried your suggestion. I recieved an error message stating: "OLE error 0x80020006: Unknown name." I established the connection with this:

oAcc=createobject("access.application&quot
oAcc.opencurrentdatabase(myDatabase)

lnAffected=0
lcSQL="INSERT INTO Table2 SELECT * FROM Table1"
oAcc.Execute(lcSQL,@lnAffected)
IF lnAffected > 0
WAIT WIND "Data Transferred: "+TRAN(lnAffected)+" Records"
ENDIF

oAcc.closecurrentdatabase()
oAcc.quit
release oAcc

 

[jonscott8]

Hi Steve,

Your initial request was to execute the query using ADO within VFP. Your example illustrates you are attempting to automate Access. It's possible to do it both ways.
Automating Access:

lcSQL="INSERT INTO Table2 SELECT * FROM Table1"
mydatabase="C:\mypath\mydatabase.mdb"
oAcc=createobject("access.application&quot
oAcc.opencurrentdatabase(myDatabase)
oAcc.DoCmd.RunSQL(lcSQL)
oAcc.closecurrentdatabase()
oAcc.quit
release oAcc

Using ADO:

lcConnect="Provider=Microsoft.Jet.OLEDB.4.0;Password=mypass;User ID=myuser;Data Source=C:\mypath\mydatabase.mdb;Persist Security Info=True"
lnAffected=0
lcSQL="INSERT INTO Table2 SELECT * FROM Table1"
oConnection = CreateObject('ADODB.Connection')
oConnection.Open(lcConnect)
IF oConnection.State = 1 && connected sucessfully
oConnection.Execute(lcSQL,@lnAffected)
IF lnAffected > 0
WAIT WIND "Data Transfer: "+TRAN(lnAffected)+"Records"
ENDIF
ELSE
WAIT WIND "Unable to connect"
ENDIF

Of the two approaches, I'd recommend the latter because it creates less overhead on memory and doesnt require Access on the target machine, only MDAC. Jon Hawkins

 

[spayne]

Jon,

I have opted to use ADO as per your suggestion. I get an "Incomplete query clause" when I execute this SQL statement:

lcSQL="INSERT INTO Table2 SELECT * FROM Table1"

Since I am updating a couple of tables with this, my code looks like this:

lcSQL="INSERT INTO '" + TableB + "' SELECT * FROM '" + TableA + "'"

TableB's name contains hyphens ("4-2001-info&quotand TableA's name contains spaces ("info sheet&quot. I think this is the problem, but I can't change the tables' names. Any ideas?

 

[jonscott8]

Try:
lcSQL="INSERT INTO [" + TableB + "] SELECT * FROM [" + TableA + "]" Jon Hawkins

 

[spayne]

One more question. Is there any simple way of creating one Access table from another Access table as long as the first table has the identical structure of the second? I would like to avoid the whole "CREATE TABLE myTable (myFieldA Integer(6), myFieldB Char(10)...)" thing, since there are nearly 50 fields in one table, and the stucture is just sitting there begging to be used.

Thanks,
Steve

 

[jonscott8]

I thought this sounded familiar.

Have you explored my suggestion from thread184-54784? Jon Hawkins

 

[spayne]

I should give you two more stars for that one! I was exploring several ways of developing this and I decided to go with someones suggestion from the Universal Thread. Only now do I see the error of my ways. I bow to your superiority! Anyway, thanks again.

Steve