Access 2007 DoCmd.RunCommand acCmdImport - Alternative? 1

[kjv1611]

I built a database previously in Access 2003... or maybe 2002. Either way, it's been working flawlessly, but I just noticed something today with that database.

One big piece of that database is importing spreadsheets, text files, tables from other databases on an as needed basis.

The way I have it setup is using a command button to run this command:

DoCmd.RunCommand acCmdImport

However, when I tried to run this database for the first time with Access 2007, I got an error:

Error Number = 2002
Error Description = You tried to perform an operation involving a function or feature that was not installed in this version of Microsoft Office Access.

And as a reference, I did verify that apparently the acCmdImport function will not function in Access 2007:

http://technet.microsoft.com/en-us/library/cc179181.aspx

So my question is, does anyone have any suggestions off hand for an alternative? And in case it helps, I'll post the full small bit of code I was using behind the affected form button:

Private Sub cmdImport_Click()
    On Error GoTo cmdImport_Err
    [b][blue][highlight gray]DoCmd.RunCommand acCmdImport[/highlight][/blue][/b]
    cboTables.Requery
    Exit Sub
cmdImport_Err:
    If Err.Number = 2501 Then
    Else
        MsgBox "There was an error in the database: " & Chr(13) & Chr(13) & _
                    "Error Number: " & Err.Number & Chr(13) & _
                    "Error Description:" & Chr(13) & _
                    "   " & Err.Description, vbCritical, "Error Encountered!"
    End If
    cboTables.Requery
End Sub

Thanks in advance for any suggestions, references, examples, etc.

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

From the article you referenced...

Import RunCommand
Description: The DoCmd.RunCommand acCmdImport object model call and the Import RunCommand macro are no longer supported. An error message appears when this object model call or the macro RunCommand call is made. They have been replaced by more specific ImportAttach commands.

I'd look up ImportAttach in help. I don't have 2007.

 

[kjv1611]

Thanks!


--

"If to err is human, then I must be some kind of human!" -Me

 

[kjv1611]

Well, actually, I've now been looking for info on just that, and there's nothing in the Access VBA help file, nor is there much on the web - so far... hmmm...

This is leading me to think that they used the wrong terminology or something...

I'll try searching with a space between and see if that makes a difference..

If I don't get it sorted out, I'll probably just use the FileDialog deal.

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

In previous versions I've always used docmd.tranferspreadsheet to import Excel files. I would expect that exists still.

 

[kjv1611]

Yes, I believe that one does. What I was already thinking before I noticed you had replied was doing it this way:

1. Use the File Dialog to get the file name(s) into a table.
2. Use the Transferspreadsheet or ..database command within a loop (in case multiple tables/sheets) to handle it that way..

Thanks for the advice. There are many different things to get used to in Office 2007, but the more I use it at work, the better I like it for big or advanced stuff. It seems more powerful in some ways. And for the most part, as I get used to it the ribbon is great as well.

That's a tad bit off from the subject, but thought I'd mention it in case you were considering (if you have the choice) moving to '07.

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

My brief testing showed you needed more than 1 GB of ram to use it effectively on XP. It will likely be 2011 before some environments have that kind of Ram in all systems.

Thankfully that is not my concern where I work now

 

[kjv1611]

Yeah. Honestly, with the price of RAM these days, if the systems used anywhere can handle the RAM, my opinion is they shouldn't be using less than 2GB per machine anyway. Ram is just dirt cheap! There is no excuse.

I had a machine (4.5 years or more old) running a P4D CPU, 1 GB Ram, and the HDD had 2MB cache. It was S.....L....O....W!!!!

Now I'm running a Celeron based system, but it's on the Core architecture, 2GB Ram, and the HDD has an 8MB cache, I believe (which is still small by currently available drives), and WOW what a difference. This one is a Dell Optiplex 755 (ultra small form factor), the last one was a GX260.

But yes, I'd highly recommend using at least 2GB of Ram, myself. :0)

--

"If to err is human, then I must be some kind of human!" -Me

 

[lameid]

2 GB of Ram is the new minumum I agree... But Would you upgrade a 4 year old machine so it might not be replaced in year 5? And older Ram is more expensive. DDR2 is cheap now... DDR is not overly cost prohibitve but why not buy a new system sooner instead. I mean 1GB DDR 3200 has been swinging around $35 - $50. I know because my Media Center computer would use that as well as an old system I have at home.

Personally, I say replace every 3-4 years depending on the price point you want to buy at.

 

[kjv1611]

That is a good point. At work, I mentioned that I had gotten to the point to where I needed a new machine, or at least a RAM upgrade. Thankfully, they went ahead and did a "newer" machine. So it's a bigger difference than just a RAM upgrade for sure!

Here's what I went from/to at work:

Old PC:
CPU = Intel Pentium 4D
RAM = 1GB DDR
HDD = 80GB, 2MB Cache, 7200RPM

New PC:
CPU = Intel Celeron 430 (based on Core archtecture)
RAM = 2 GB DDR2
HDD = 80GB, 8MB Cache, 7200RPM

That machine is not brand new, and it's not exactly cutting edge, but my oh my what a difference over the old one!

And the old one was a mid-sized tower, whereas this one is the ultra small form factor of the Optiplex 755 line - so runs nice and quiet, and takes up VERY little desk space - I like!

Well, nonetheless, that's off topic again.

--

"If to err is human, then I must be some kind of human!" -Me