Common Dialog box error on win7 but not xp

[helper11]

Hello,

I have the following code that lets the user read in a text file and backs up the table and then addes the data to the main table and then removes the imported temp table, works great on my xp computer using access 2003, tried it on my laptop running win7 and access 2003 and it comes up with a Active X component cant create object 429 error, Please advise on how to fix this:

Dim fd As FileDialog


'Opens dialog box for file selection
Set ObjFSO = CreateObject("UserAccounts.CommonDialog")
'Sets Type of file to look for
ObjFSO.Filter = "VBScripts|*.vbs|Text Documents|*.txt|All Files|*.*"
ObjFSO.FilterIndex = 3
'Sets Directory for initial search to start in
ObjFSO.InitialDir = "C:\Windows\Temp\"

If fld.Show = True Then
DoCmd.CopyObject , "Sales_download_file_ao_copy", acTable, "Sales_download_file_ao"
'Transfers file using acImportDelim profile previously set through manually importing into table
DoCmd.TransferText acImportDelim, "Sales_download_file_ao Import Specification", "Imported", ObjFSO.Filename, True
DoCmd.OpenQuery "Imported Query", , acReadOnly
DoCmd.OpenQuery "Imported Query Bad Dates", , acReadOnly
MsgBox " Completed"
DoCmd.DeleteObject acTable, "Imported"
End If

 

[helper11]

Hello,

Actually, I think it is something with the way it handles the dialog in windows 7. I did get it to work but had to use a different method. I am including it below incase it will help anyone else in the future:

Dim fd As FileDialog, strFilesSelected As String

Set fd = Application.FileDialog(msoFileDialogFilePicker)

'Declare a variable to contain the path, it must be a Variant because
'of the For..Each construct requires it
Dim vrtSelectedItem As Variant

With fd
.AllowMultiSelect = False
.title = "Browse Files"
If .Show = -1 Then
For Each vrtSelectedItem In .SelectedItems
strFilesSelected = strFilesSelected & vrtSelectedItem '& vbCrLf
Next vrtSelectedItem
DoCmd.CopyObject , "Sales_download_file_ao_copy", acTable, "Sales_download_file_ao"
' Transfers file using acImportDelim profile previously set through manually importing into table

DoCmd.TransferText acImportDelim, "Sales_download_file_ao Import Specification", "Imported", strFilesSelected, True
DoCmd.OpenQuery "Imported Query", , acReadOnly
DoCmd.OpenQuery "Imported Query Bad Dates", , acReadOnly
MsgBox " File Import Completed "
DoCmd.DeleteObject acTable, "Imported"
End If
End With

Set fd = Nothing

Thanks for your time....

 

[ghudson]

Try a custom browser like in my old Browse [Find a directory or file] sample. Should work in any version of Windows and Access since it does not require any references or active x components.

 

[HiTechCoach]

I like to call the Standard Windos Dialog using an Windows API.

It should work with all versions of Access and Windows.

Call the standard Windows File Open/Save dialog box

 

[boblarson]

I like to call the Standard Windos Dialog using an Windows API.

It should work with all versions of Access and Windows.

Call the standard Windows File Open/Save dialog box

Except for 64 Bit Windows

 

[HiTechCoach]

Except for 64 Bit Windows

Hmm ... Interesting.

I just tested with Access 2007 SP2 on a Laptop running Windows 7 Home Premium . The Properties show System Type: 64-bit Operating System.

from systeminfo

OS Name: Microsoft Windows 7 Home Premiu
OS Version: 6.1.7600 N/A Build 7600
OS Manufacturer: Microsoft Corporation
OS Configuration: Standalone Workstation
System Boot Time: 5/26/2010, 2:15:50 AM
System Manufacturer: Hewlett-Packard
System Type: x64-based PC



I downloaded ghudson's example and converted to Access 2007. Ran just fine.


I put the code in the link into Access 2007 The code ran just fine.

Why does it run? Am I missing something?


Do you have a suggestions on VBA code to determine OS type?

 

[boblarson]

You are running Access 32 bit. But if you run Access 64 bit, you will find the API does not work as written.

 

[ghudson]

Hmm ... Interesting.

I downloaded ghudson's example and converted to Access 2007. Ran just fine.

Thanks for confirming that my old browser sample works in 64 bit Windows 7 !!!

 

[HiTechCoach]

You are running Access 32 bit. But if you run Access 64 bit, you will find the API does not work as written.

Got it now. I misunderstood that "64 Bit Windows" was referring to the version of Access.

 

[ghudson]

Hmm ... Interesting.

Do you have a suggestions on VBA code to determine OS type?

Get Windows Version from MS Access 2007