Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access
Password Reminder
Register
| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old October 17th, 2006, 02:15 AM
Authorized User
Points: 172, Level: 3
Points: 172, Level: 3 Points: 172, Level: 3 Points: 172, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2005
Location: Singapore, , Singapore.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default MS Access Query

Hi,

I'm running some statistics compilation using Access. I'll need to import data from .csv format to Access before generating the reports. The problem is that the data may sometimes be not available for import. How can I include in Access a condition such that it will only execute the macro to import the .csv file only when the file is available?

Pls advise.

Many thanks.

Reply With Quote
  #2 (permalink)  
Old October 17th, 2006, 02:11 PM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

I don't have the script in front of me, but you can check for the existence of the file before you do anything using Scripting.FileSystemObject and then If objFile.FileExists()

I don't know the syntax off hand but could get it to you tomorrow.




mmcdonal
Reply With Quote
  #3 (permalink)  
Old October 17th, 2006, 07:56 PM
Authorized User
Points: 172, Level: 3
Points: 172, Level: 3 Points: 172, Level: 3 Points: 172, Level: 3
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2005
Location: Singapore, , Singapore.
Posts: 32
Thanks: 0
Thanked 0 Times in 0 Posts
Default

hi, many thanks. look forward to receiving the script from you tomorrow.

Reply With Quote
  #4 (permalink)  
Old October 18th, 2006, 06:50 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Okay, this will work:

Dim objFSO 'no variable type declaration or use Variant

Set objFSO = CreateObject("Scripting.FileSystemObject")
If objFSO.FileExists("C:\Path\Filename.csv") Then

   ...do your processing here
Else
   MsgBox("File does not exist")
   Exit Sub
End If

This VBScript code should work within Access unless your security settings prevent scripts from working outside of the application or your system settings prevent scripts from scanning file systems. This is rare, however, since most people use default settings.

When the user opens Access, they will want to make sure they don't put the app in sandbox mode.

Anyway, I use this sort of scripting all the time in my apps.

Did this work for you?



mmcdonal
Reply With Quote
  #5 (permalink)  
Old October 18th, 2006, 06:54 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

You can also add this to the script to make it more manageable:

Dim sFile As String

sFile = "C:\path\filename.csv"

Then...

If objFSO.FileExists(sFile) Then
...

This will allow you to take the file name from various places as a variable instead of hard coding it.

HTH

mmcdonal
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
MS Access Query neha2007 SQL Language 1 July 12th, 2007 10:11 AM
ms access query umeshtheone VB Databases Basics 1 June 1st, 2007 10:27 AM
MS Access Query rsm42 ASP.NET 1.0 and 1.1 Basics 2 March 20th, 2007 12:32 PM
I am trying to convert a MS Access Query to a SQL? WebLadyBug SQL Server ASP 3 March 9th, 2007 12:39 PM
Ms Access stored query problem turker Classic ASP Databases 0 December 27th, 2005 01:38 PM



All times are GMT -4. The time now is 06:01 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.