Importing DBF Databases

[Dustin Ventin]

I need allow users to import DBF databases into my Access application.
However, because this application is going on the market, I cannot allow them
to directly alter anything, and I therefore need a way to link my forms to an
imported table that (theoretically), could be named anything.

To overcome this, I am trying to get Access to import the data, copy it over
to a table that I KNOW will be named correctly (so I can link forms to it),
and then delete the original imported table. I figure its OK to hardcode an
imported table in this way, because it should be named P3 (because it's
actually an exported file from Primavera, and automatically named), and if I
import it, take the data, and delete it all at once, other users shouldn't
have a chance of importing thier own P3.DBF, and Access won't start
concatonating numbers.

So far, I have two attempts:

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

This one doesn't work because it says the driver is incorrect or cannot be
found when I try the objConn.Open line, and I can't find anywhere where
Microsoft explains where to find its drivers. Additionally, I suspect this
won't work because I'm trying to run a SQL string that involves two seperate
data sources.

My other idea is this:

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

This one works really well, except that the acCmdDeleteTable command errors
out and says it can't be run at this time. (I'm trying to delete the P3 table
so that anyone else who tries to import can still use the P3 table name.
Hopefully, two users don't press the button at the same time.)

So, any thoughts or suggestions? Is there a way to fix one of these
solutions, or perhaps ever a better solution, period?

I greatly appreciate your assistance.

Dustin

 

[Dustin Ventin]

The Microsoft Jet Database could not find the object ". Make sure the object
exists and that you spell the name and path correctly.


Of course, once the user has made the required changes, I need to export.
I'm trying to use the following code:

DoCmd.TransferDatabase acExport, "Dbase IV", "C:\P3WIN\P3OUT\", acTable,
"tblPrimaveraDetail", "P3"

I've also tried:

DoCmd.TransferDatabase acExport, "Dbase IV", "C:\P3WIN\P3OUT\P3.DBF",
acTable, "tblPrimaveraDetail", "P3"

I've tried exporting a query with the required data as well (as a macro):

DoCmd.TransferDatabase acExport, "Dbase IV", "C:\P3WIN\P3OUT\P3.DBF",
acTable, "qryExportEstimate2Primavera", "P3"

This says: "Cannot define a field more than once."

So I took out the destination. It's not required anyway.

DoCmd.TransferDatabase acExport, "Dbase IV", "C:\P3WIN\P3OUT\P3.DBF",
acTable, "qryExportEstimate2Primavera"

It says: "Syntax error in query. Incomplete query clause."

The query iteself runs fine, and looks like this:

SELECT tblPrimaveraDetail.PD_ActivityID,
tblPrimaveraDetail.PD_ActivityDescription,
tblPrimaveraDetail.PD_BudgetQuantity, tblPrimaveraDetail.PD_BudgetCost,
tblPrimaveraDetail.PD_Resource
FROM tblPrimaveraDetail;

Any ideas? I could really use some help on this, because I have higher-ups
- shall we say - VERY interested in my getting this done soon. Anything you
guys could do to help would be greatly appreciated.

Thanks!

Dustin

 

[Dustin Ventin]

Is this the wrong board for these questions? If so, please let me know,
because I really need to get this figured out.

Thanks!

Dustin

 

[Dustin Ventin]

Okay, I've figured out the first problem. I can import no problem now.

Now all I need to be able to do is export a query with the revised data in
it as a DBF file. I've tried all manner of TransferDatabase statements, and
none of them work. All I need is a way to export a the results of a query
into it's own seperate DBF database.

It should be just one line of code, right?

Thanks!

Dustin

 

[Charles E. Vopicka]

i need to do some exporting as well and have had problems in the past
with dbase files. i just worked this out and see if it works for you


DoCmd.TransferDatabase acExport, "dBase III", "c:\\", acTable,
"Titles", "test.dbf"


so i am exporting to a dbase 3 file on my c drive note the \\ instead of
\ and i was using a table but i would think query would work just fine
and it was a table called titles to a file test.dbf.


i ran that line and a file c:\test.dbf was created and had my data from
my titles table.

Okay, I've figured out the first problem. I can import no problem now.

Now all I need to be able to do is export a query with the revised data in
it as a DBF file. I've tried all manner of TransferDatabase statements, and
none of them work. All I need is a way to export a the results of a query
into it's own seperate DBF database.

It should be just one line of code, right?

Thanks!

Dustin

--
Charles E. Vopicka's (Chuck) : (e-mail address removed)

Database Management, GIS Specialist and Research Assistant

Forest Biometrics Research Institute
University of Montana - College of Forestry and Conservation
Missoula, MT 59812
United States of America

Phone:
(406)243-4526
(406)243-4264
(406)549-0647 (Home)

HAVE A NICE DAY (-:

"UNLESS" (The Lorax, by Dr. Seuss)

 

[Cindy Winegarden]

Please note that older DBFs have a 10-character limit on table and field
names. Since the OP was moving the data to an Access table first he should
be aware of this when he designs his Access table.

 

[Charles E. Vopicka]

i think also there can be no spaces and only letters and numbers. that
is what i have noticed from my experiences. access is much more
forgiving for field names.

Please note that older DBFs have a 10-character limit on table and field
names. Since the OP was moving the data to an Access table first he should
be aware of this when he designs his Access table.

--
Charles E. Vopicka's (Chuck) : (e-mail address removed)

Database Management, GIS Specialist and Research Assistant

Forest Biometrics Research Institute
University of Montana - College of Forestry and Conservation
Missoula, MT 59812
United States of America

Phone:
(406)243-4526
(406)243-4264
(406)549-0647 (Home)

HAVE A NICE DAY (-:

"UNLESS" (The Lorax, by Dr. Seuss)

 

[Dustin Ventin]

I essentially used your code as much as possible, for the following:

DoCmd.TransferDatabase acExport, "dBase IV", "c:\\", acTable,
"qryExportEstimate2Primavera", "test.dbf"

I get the error message: "Cannot define field more than once."

Any ideas?

Dustin

 

[Dustin Ventin]

I see...so THAT'S the error message that comes up when a field name is longer
than 10 characters...very informative!

Anyway, it's working now! Thanks for all the help!

Dustin

 

[Cindy Winegarden]

Hi Dustin,

To be more specific, that's what happens when you have two field names that
are the same for the first ten characters so that when they are truncated
they end up with the same value.

I'm glad you have it working now!

 

[Dustin Ventin]

Sorry, I guess my sarcasm was especially strong that day.

Thanks for all the help!

Dustin

 

[Dustin Ventin]

I'm back!

I've realized that, as opposed to how I was doing it, using TransferDatabase
is probably a better way of doing things. Therefore, I'm using the following
code to try and import a DBF file:

'Dim strFilter As String
'Dim strPrimaveraPath As String

'strFilter = ahtAddFilterItem(strFilter, "DBase (*.DBF)", "*.DBF")
'strPrimaveraPath = ahtCommonFileOpenSave( _
Filter:=strFilter, OpenFile:=True, _
DialogTitle:="Select a Copy of Stocastic", _
Flags:=ahtOFN_HIDEREADONLY, _
InitialDir:=Application.CurrentProject.Path & "\" _
)

MsgBox ("Please remember that your external Primavera data must be in a
DBF file called " & txtPrimaveraFile & ".")

'DoCmd.TransferDatabase , acImport, , txtPrimaveraFile & ".dbf",
acTable, "P3", "tblImportedData"

txtPrimaveraFile is set using a call to the Windows API that allows users to
browse and select a file. That code works fine.

However, when I try to run this, after I select the file I want to import,
it comes up and says: "Error Number 13. Type Mismatch."

Exactly what's going on here? Thanks!

Dustin

 

[John Nurick]

Hi Dustin,

Of the code you posted, everything has been commented out except the
MsgBox statement. The only way I can imagine that that could raise a
type mismatch error is if the undeclared variable txtPrimaveraFile
contains a value that the MsgBox code cannot convert to a string. For
example, this

Dim txtPrimaveraFile 'undeclared variables and variables without
'declared types are actually Variants
txtPrimaveraFile = Array("a", "b", "c")
MsgBox ("Please remember that your external Primavera" _
& " data must be in a DBF file called " & txtPrimaveraFile & ".")

raises a type mismatch error because VBA can't concatenate an array into
a string. But there's nothing in the code you posted that assigns *any*
value to txtPrimaveraFile.

So: what you've showed us can't be the code you're actually using.

 

[Dustin Ventin]

Try the exact same code, not commented out.

Thanks,

Dustin

 

[John Nurick]

Next time, please post the code that is actually causing the problem,
and not a bastardised version of it.

You're getting the type mismatch because you are passing the undeclared
and uninitialised variable
txtPrimaveraFile
to TransferDatabase. Because it's undeclared it's a Variant, and because
it's an uninitialised Variant its value is Empty - but TransferDatabase
requires a String. Presumably you intended to pass
strPrimaveraPath
instead.

You should declare
Option Explicit
at the beginning of every VBA module. Doing that means that undeclared
variables will give you a compile error and save a lot of grief with
problems like that. Checking "Require Variable Declaration" in the VBA
editor's Options dialog will ensure that Option Explicit is included in
all modules created in future.

 

[Dustin Ventin]

txtPrimaveraFile is a reference directly to a textbox that has the name of
the database file it should be linking to. Are you sure this is the problem?

Dustin

 

[John Nurick]

Of course I'm not sure, because there's every possibility that there's
yet another relevant fact you haven't mentioned, and the snippet you've
provided is quite confusing (e.g. if you've already got the filename in
a textbox, why are you using the File Dialog to ask the user to provide
a filename?). Many if not most professional coders use an explicit
syntax such as
Me.txtXXX
or
Me.txtXXX.Value
to refer to the contents of a control on the form so as to avoid
confusion with variables.

Anyway, the next problem is in the TransferDatabase call, where you've
got the wrong arguments in the wrong order. The comma before acImport
means you're passing the value of the constant acImport (i.e. the number
0) where TransferDatabase expects a string describing the type of
database (e.g. "dBASE IV"). This DatabaseType argument is optional if
you're importing from another Access database, but you have to specify
the type if you're importing from something else.

Also, you're passing the whole filespec in the DatabaseName argument.
This is a gotcha when importing from dBASE (and Paradox and a few
others); I normally do this with menu commands, which conceal it from
you, so didn't spot it until today. If you read the Help on the VBA
TransferDatabase method carefully, you'll see it refers you to Help for
the TransferDatabase macro action for further details. There, you'll
find that there's a little twist when importing from dBASE. For the
DatabaseName argument, you have to provide the *folder*, not the file.
Then you provide the filename separately in the Source or Destination
argument (for importing or exporting respectively).

 

[Dustin Ventin]

Thanks, that works excellantly.

Now, is there any way to have the imported data append to the table I tell
it to, instead of creating a new copy of the table every time?

The way it works right now:

DoCmd.TransferDatabase acImport, "dBASE IV", "C:\P3WIN\P3OUT\", acTable,
txtPrimaveraFile, "P3"

....it creates a new copy of "P3" every time, P31, P32, P33, which obviously
works terribly for an application. How can I make it so that these records
all append directly into P3?

Dustin

 

[John Nurick]

Access will create a new table in the way you describe if there is any
difference in structure (even one different field name) between the
existing Access table and the table you are importing: could that be the
problem.

Otherwise, I don't know just what's happening. Perhaps the way round it
would be to link to the dBase files instead of trying to import them,
and use an append query to move the data from each linked table to your
one "permanent" table.