How Do I Programmatically Append From DBF?

[Guest]

OK, here's the long story:

I'm writing a Project Management application that I want to integrate with
Primavera. I want to be able to import Primavera data, save it into a table
in my database, allow users to work with it there, and then export it again
to Primavera. To do this, the Primavera file must be saved in DBF format,
which is no big problem, because Primavera can do that automatically.

The problem is, I don't want users to have to tell the application which
table to import to, and then have to programmatically allow them to link all
my forms to some new table they may have named arbitrarily. Instead, I'd
rather import the DBF data using a RunSQL command into a table I've already
created, designed for the purpose, and linked my forms to appropriately.
Therefore, I need a two-click system.

1) Select the DBF file to import from.
2) Import the data from the DBF file to my pre-made table.

I'm fairly certain the first option is easy. The second option is not (for
me). Here is my code so far...I'm starting slow, just trying to import the
Task name from the Primavera DBF file.

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim sPath As String, sFile As String, sSQL As String

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

sPath = "C:\P3WIN\P3OUT"
sFile = "P3.DBF"

sSQL = "SELECT * FROM P3"
objConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" &
"DriverID=277;" & "Dbq=" & sPath '"Dbq=c:\somepath"
objRS.Open sSQL, objConn, , , adCmdText

Do While Not objRS.BOF And Not objRS.EOF
DoCmd.RunSQL "INSERT INTO tblPrimaveraDetail (
PD_ActivityDescription ) SELECT TITLE FROM objRS;"

Loop

The hard part is the RunSQL command, I believe. How do I tell it to select
it's data from the recordset I have created? Is this the best way to do it?
Does anyone have any comments or suggestions?

Thanks!

Dustin

 

[John Nurick]

Hi Dustin,

INSERT INTO tblPrimaveraDetail
(PD_ActivityDescription)
SELECT TITLE
FROM objRS;

AFAIK that can't work, because objRS is an ADODB.Recordset and an SQL
FROM clause requires either a table or a query. Also, using RunSQL is
almost never a good idea when you're working in VBA with Connection or
Database objecets: use their Execute methods instead.

So looks as if you need to forget about objRS and do something like

sSQL = "INSERT INTO (PD_ActivityDescription) " _
& "SELECT TITLE FROM P3;"
objConn.Open blah blah
objConn.Execute sSQL

 

[Guest]

Ok, I'm trying your way, and my code looks like this:

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim sPath As String, sFile As String, sSQL As String

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

sPath = "C:\P3WIN\P3OUT\P3.DBF"

sSQL = "INSERT INTO tblPrimaveraDetail (PD_ActivityDescription) SELECT
TITLE FROM P3;"

objConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & "DriverID=277;" &
"Dbq=" & sPath

objConn.Execute sSQL


However, when it comes to opening the file, it errors out:

[Microsoft][ODBC dbase driver] '(unknown)' is not a valid path. Make sure
that the path name is spelled correctly and that you are connected to the
server on which the file resides.

I'm assuming that I've incorrectly directed the connection when looking for
the driver required to open DBF files...but for the life of me I can't find
where Microsoft explains where it's drivers are kept. Connection strings are
kind of one of my weaknesses, I guess, and MSDN is not exceptionally clear on
what paths lead to what drivers.

Any ideas? Thanks!

Dustin

 

[Guest]

Also, because my query needs to connect to two seperate data sources at once
(my database and the external database), I get the feeling it won't work. In
fact, what I want to do may be impossible.

Any thoughts? Thanks!

Dustin

 

[John Nurick]

I'm no good at connection strings either, but here's a man who is:
http://www.carlprothman.net/Default.aspx?tabid=81

Ok, I'm trying your way, and my code looks like this:

Dim objConn As ADODB.Connection
Dim objRS As ADODB.Recordset
Dim sPath As String, sFile As String, sSQL As String

Set objConn = New ADODB.Connection
Set objRS = New ADODB.Recordset

sPath = "C:\P3WIN\P3OUT\P3.DBF"

sSQL = "INSERT INTO tblPrimaveraDetail (PD_ActivityDescription) SELECT
TITLE FROM P3;"

objConn.Open "Driver={Microsoft dBASE Driver (*.dbf)};" & "DriverID=277;" &
"Dbq=" & sPath

objConn.Execute sSQL


However, when it comes to opening the file, it errors out:

[Microsoft][ODBC dbase driver] '(unknown)' is not a valid path. Make sure
that the path name is spelled correctly and that you are connected to the
server on which the file resides.

I'm assuming that I've incorrectly directed the connection when looking for
the driver required to open DBF files...but for the life of me I can't find
where Microsoft explains where it's drivers are kept. Connection strings are
kind of one of my weaknesses, I guess, and MSDN is not exceptionally clear on
what paths lead to what drivers.

Any ideas? Thanks!

Dustin

 

[Guest]

Actually, I'm trying a different approach now:

DoCmd.RunCommand acCmdImport

DoCmd.RunSQL "INSERT INTO tblPrimaveraDetail ( PD_ActivityID,
PD_ActivityDescription, PD_BudgetCost, PD_BudgetQuantity, PD_Resource )
SELECT P3.ACT, P3.TITLE, P3.BC, P3.BQ, P3.RES FROM P3;"

DoCmd.RunCommand acCmdDeleteTable

I want to import the DBF file automatically, copy the data from it into the
table I can link my forms to and know that the information is named
correctly, and then delete the imported table. I want to do this so that the
entire process takes only seconds, and other users also importing from P3 are
unlikely to do so at the same time and overwrite the P3.DBF file.

However, I am having two problems:

1) No matter what, once the acCmdImport successfully imports, it does back
to the Import screen, and the only way to close it is to select 'close',
which the code interprets as an error and throws an error. And no,
SetWarnings (False) doesn't seem to work.

2) When it runs DoCmd.RunCommand acCmdDeleteTable, it says that the command
cannot be carried out.

Any ideas? Thanks!

Dustin

 

[John Nurick]

It's certainly not impossible. I suggest you start a new thread in the
External Data newsgroup (microsoft.public.access.externaldata).

 

[Guest]

Thanks...I'll try that!

Dustin

It's certainly not impossible. I suggest you start a new thread in the
External Data newsgroup (microsoft.public.access.externaldata).