|
|
Our Hot Pick: Rising Antivirus 2006 - Certified by TUV & Checkmark! Get 10% discount by entering this coupon code: ONDISCOUNT10
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
|
|
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
|
|
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" wrote:
[Quoted Text] > 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
|
|
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
|
|
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.
Dustin Ventin wrote:
[Quoted Text] > 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) : chuck[ at ]forestbiometrics.com
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)
|
|
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.
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy[ at ]cindywinegarden.com
[Quoted Text] > DoCmd.TransferDatabase acExport, "dBase III", "c:\\", acTable,
> "Titles", "test.dbf"
>> Now all I need to be able to do is export a query with the revised data
>> in it as a DBF file. .....
|
|
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.
Cindy Winegarden wrote:
[Quoted Text] > 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) : chuck[ at ]forestbiometrics.com
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)
|
|
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
|
|
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
"Dustin Ventin" wrote:
[Quoted Text] > 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
|
|
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!
--
Cindy Winegarden MCSD, Microsoft Most Valuable Professional
cindy[ at ]cindywinegarden.com
"Dustin Ventin" <DustinVentin[ at ]discussions.microsoft.com> wrote in message
news:D07470D1-1474-4E1A-A838-A0AF05FFC1A3[ at ]microsoft.com...
[Quoted Text] >I see...so THAT'S the error message that comes up when a field name is
>longer
> than 10 characters...very informative!
|
|
Sorry, I guess my sarcasm was especially strong that day.
Thanks for all the help!
Dustin
"Cindy Winegarden" wrote:
[Quoted Text] > 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!
>
> --
> Cindy Winegarden MCSD, Microsoft Most Valuable Professional
> cindy[ at ]cindywinegarden.com
>
>
> "Dustin Ventin" <DustinVentin[ at ]discussions.microsoft.com> wrote in message
> news:D07470D1-1474-4E1A-A838-A0AF05FFC1A3[ at ]microsoft.com...
> >I see...so THAT'S the error message that comes up when a field name is
> >longer
> > than 10 characters...very informative!
>
>
>
|
|
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
|
|
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.
On Thu, 17 Aug 2006 16:13:01 -0700, Dustin Ventin
<DustinVentin[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >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 [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
Try the exact same code, not commented out.
Thanks,
Dustin
"John Nurick" wrote:
[Quoted Text] > 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.
>
> On Thu, 17 Aug 2006 16:13:01 -0700, Dustin Ventin
> <DustinVentin[ at ]discussions.microsoft.com> wrote:
>
> >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 [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
|
|
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.
On Fri, 18 Aug 2006 10:12:01 -0700, Dustin Ventin
<DustinVentin[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >Try the exact same code, not commented out.
>
>Thanks,
>
>Dustin
>
>"John Nurick" wrote:
>
>> 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.
>>
>> On Thu, 17 Aug 2006 16:13:01 -0700, Dustin Ventin
>> <DustinVentin[ at ]discussions.microsoft.com> wrote:
>>
>> >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 [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
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" wrote:
[Quoted Text] >
> 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.
>
> On Fri, 18 Aug 2006 10:12:01 -0700, Dustin Ventin
> <DustinVentin[ at ]discussions.microsoft.com> wrote:
>
> >Try the exact same code, not commented out.
> >
> >Thanks,
> >
> >Dustin
> >
> >"John Nurick" wrote:
> >
> >> 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.
> >>
> >> On Thu, 17 Aug 2006 16:13:01 -0700, Dustin Ventin
> >> <DustinVentin[ at ]discussions.microsoft.com> wrote:
> >>
> >> >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 [Microsoft Access MVP]
> >>
> >> Please respond in the newgroup and not by email.
> >>
>
> --
> John Nurick [Microsoft Access MVP]
>
> Please respond in the newgroup and not by email.
>
|
|
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).
On Fri, 18 Aug 2006 14:28:01 -0700, Dustin Ventin
<DustinVentin[ at ]discussions.microsoft.com> wrote:
[Quoted Text] >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" wrote:
>
>>
>> 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.
>>
>> On Fri, 18 Aug 2006 10:12:01 -0700, Dustin Ventin
>> <DustinVentin[ at ]discussions.microsoft.com> wrote:
>>
>> >Try the exact same code, not commented out.
>> >
>> >Thanks,
>> >
>> >Dustin
>> >
>> >"John Nurick" wrote:
>> >
>> >> 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.
>> >>
>> >> On Thu, 17 Aug 2006 16:13:01 -0700, Dustin Ventin
>> >> <DustinVentin[ at ]discussions.microsoft.com> wrote:
>> >>
>> >> >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 [Microsoft Access MVP]
>> >>
>> >> Please respond in the newgroup and not by email.
>> >>
>>
>> --
>> John Nurick [Microsoft Access MVP]
>>
>> Please respond in the newgroup and not by email.
>>
--
John Nurick [Microsoft Access MVP]
Please respond in the newgroup and not by email.
|
|
|