Go Back   Access World Forums > Microsoft Access Discussion > Modules & VBA

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 06-30-2005, 08:22 AM   #1
Dan_T
Registered User
 
Join Date: Jul 2004
Location: Cornwall, UK.
Posts: 116
Thanks: 0
Thanked 0 Times in 0 Posts
Dan_T is on a distinguished road
Create a function from a text file.

Hi can someone point me in the right direction of this.

I wish to, in code, create a VBA function from the contents of a text file. Essentially I want to be able to see if a text file exists. If it does then take the contents to create a function. The bit I am lost on is how do I create a new function in code.

Many thanks,

Dan.

Dan_T is offline   Reply With Quote
Old 06-30-2005, 09:12 AM   #2
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
First, search this forum on File System Object, or Open File. This will help you see if a file exists.

Next do searches on Active Code Pane

Go on MSDN or possibly in the forum (or google) for the Active Code Pane objects. Here are some functions that might be helpful to you:

VBE.ActiveCodePane.CodeModule.CreateEventProc
VBE.ActiveCodePane.CodeModule.InsertLines
vbe.ActiveCodePane.CodeModule.AddFromFile
__________________
APP- A97/2K/XP/2K3
OS- Home: XPP Work: W2K/XPP

[SIGPIC][/SIGPIC]
If someone has helped you, please add to their reputation by clicking on the icon that looks like weigh-scales on the top right of their post
modest is offline   Reply With Quote
Old 07-01-2005, 10:46 AM   #3
Dan_T
Registered User
 
Join Date: Jul 2004
Location: Cornwall, UK.
Posts: 116
Thanks: 0
Thanked 0 Times in 0 Posts
Dan_T is on a distinguished road
Thanks Modest, its the Active Code Pane suggestion I was looking for. Its one of those ones that unless you have an incling what the function etc is called you can try all sorts of searches and be miles from the answer. The rest of it I am cool with thanks.

Many thanks

Dan

Dan_T is offline   Reply With Quote
Old 07-05-2005, 09:27 AM   #4
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
Be careful with using it... one mistake could be drastic. It can overwrite your code or mess everything up. But it is pretty powerful.
__________________
APP- A97/2K/XP/2K3
OS- Home: XPP Work: W2K/XPP

[SIGPIC][/SIGPIC]
If someone has helped you, please add to their reputation by clicking on the icon that looks like weigh-scales on the top right of their post
modest is offline   Reply With Quote
Old 07-06-2005, 01:06 AM   #5
Dan_T
Registered User
 
Join Date: Jul 2004
Location: Cornwall, UK.
Posts: 116
Thanks: 0
Thanked 0 Times in 0 Posts
Dan_T is on a distinguished road
year thanks Modest I have been treading carefully. Checking functions exist, finding where it is in the module and removing and replacing. So far just testing it with a test application. I am experiencing one minor irritation which I think I can resolve by running the function differently. However my concern is more that what I am doing is a no no.

The process is this.

1: Finds the existing version of the function.
2: Removes this version
3: creates the new version
4: Runs the new version

This work fine on the first run through, however if I call the code again the application crashes out and closes access offering me the chance to tell Mr Gates all about the error...

I don't need to run this code more than once in a session however I thought it prudent to test this out. Is there some fundamental mistake or 'No no!' to what I am doing?


Many thanks

Dan
Dan_T is offline   Reply With Quote
Old 07-06-2005, 05:19 AM   #6
ghudson
Newly Registered User
 
ghudson's Avatar
 
Join Date: Jun 2002
Location: USA
Posts: 6,199
Thanks: 1
Thanked 87 Times in 49 Posts
ghudson has a spectacular aura about ghudson has a spectacular aura about ghudson has a spectacular aura about
Are you just trying to update (replace) a module [public sub or public function]? If so then you could use the TransferText command. Check this thread out Export All Database Objects Into Text Files to see what I am talking about to replace a module.
__________________
.................................................. ......
Searching this forum or Microsoft.com or MSDN.com or Google is a great way to discover and learn the answers to your Access programming questions.

Well if it seems to be real, it's illusion...

I am using Access 2010 with Windows 7

The
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
function on this forum really does work.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

.................................................. ......
ghudson is offline   Reply With Quote
Old 07-06-2005, 05:54 AM   #7
Dan_T
Registered User
 
Join Date: Jul 2004
Location: Cornwall, UK.
Posts: 116
Thanks: 0
Thanked 0 Times in 0 Posts
Dan_T is on a distinguished road
Ah now this looks good. My situation is this. An application has to be up and running in 24 sites by the end of the month. Thats all ok, however due to someone else not delivering on time I will have to change some elements of a function and I don't want to have to roll out another version because one function has changed slightly. My thoughts were to provide the text for a function in a txt file in a specified folder, when the changes are in place update it. Now as I suspect this could be more than once I want to check and update when the application is launched.

Many thanks, I am orft to have a butcharse and play with the code you gracefully provided.

Dan_T is offline   Reply With Quote
Old 07-06-2005, 06:25 AM   #8
ghudson
Newly Registered User
 
ghudson's Avatar
 
Join Date: Jun 2002
Location: USA
Posts: 6,199
Thanks: 1
Thanked 87 Times in 49 Posts
ghudson has a spectacular aura about ghudson has a spectacular aura about ghudson has a spectacular aura about
I trust that your db is split [front end & back end] for you will always have to make modifications to your db and you will need to provide the users with an updated front end when every you make design changes.
__________________
.................................................. ......
Searching this forum or Microsoft.com or MSDN.com or Google is a great way to discover and learn the answers to your Access programming questions.

Well if it seems to be real, it's illusion...

I am using Access 2010 with Windows 7

The
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
function on this forum really does work.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

.................................................. ......
ghudson is offline   Reply With Quote
Old 07-06-2005, 06:44 AM   #9
Dan_T
Registered User
 
Join Date: Jul 2004
Location: Cornwall, UK.
Posts: 116
Thanks: 0
Thanked 0 Times in 0 Posts
Dan_T is on a distinguished road
Indeed it is split. The situation is this.

An application is heading off to 24 sites over the next month. It is built and has been tested over a month at 4 sites. However there is one small part missing, as I have been waiting on someone else to complete. Essentially I don't want to roll this out to all parties only to do it all again for the addition or change of one function. My thoughts were this, do tell me if this is wildly wrong conceptially. To provide the text for this one function, as it currently stands, in a txt file in a defined subfolder. The application will then create the function from this. Therefore when everyone else has got their act together and given me the final details I will be able to re issue the txt file via email, each user will, as it is within their skill base, replace the existing file with the new one and the application will read this in instead.

I've covered aspects of different BE locations, and differing references and their locations even in the runtime versions and this seems to be the final piece.

I have looked at the code in the link and it does indeed perform what i was hoping to achieve. Are there any draw backs? known shortfalls or issues with using this? I infact only needed the one line: Application.LoadFromText

Thanks again

Dan
Dan_T is offline   Reply With Quote
Old 07-06-2005, 07:34 AM   #10
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
Quote:
Originally Posted by ghudson
I trust that your db is split [front end & back end] for you will always have to make modifications to your db and you will need to provide the users with an updated front end when every you make design changes.
If you use a MAF link as the front end, do you still need to send the update, or would it auto-update?
__________________
APP- A97/2K/XP/2K3
OS- Home: XPP Work: W2K/XPP

[SIGPIC][/SIGPIC]
If someone has helped you, please add to their reputation by clicking on the icon that looks like weigh-scales on the top right of their post
modest is offline   Reply With Quote
Old 07-06-2005, 07:57 AM   #11
ghudson
Newly Registered User
 
ghudson's Avatar
 
Join Date: Jun 2002
Location: USA
Posts: 6,199
Thanks: 1
Thanked 87 Times in 49 Posts
ghudson has a spectacular aura about ghudson has a spectacular aura about ghudson has a spectacular aura about
Quote:
Originally Posted by modest
If you use a MAF link as the front end, do you still need to send the update, or would it auto-update?
What is a MAF link?
__________________
.................................................. ......
Searching this forum or Microsoft.com or MSDN.com or Google is a great way to discover and learn the answers to your Access programming questions.

Well if it seems to be real, it's illusion...

I am using Access 2010 with Windows 7

The
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
function on this forum really does work.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

.................................................. ......
ghudson is offline   Reply With Quote
Old 07-06-2005, 09:11 AM   #12
modest
Registered User
 
Join Date: Jan 2005
Location: VA, United States
Posts: 1,220
Thanks: 0
Thanked 2 Times in 2 Posts
modest is on a distinguished road
MAF - Microsoft Office Access Form

If you make it, you can use shortcuts to it as a frontend, and I think it will autoupdate when you make changes.
__________________
APP- A97/2K/XP/2K3
OS- Home: XPP Work: W2K/XPP

[SIGPIC][/SIGPIC]
If someone has helped you, please add to their reputation by clicking on the icon that looks like weigh-scales on the top right of their post

Last edited by modest; 07-06-2005 at 09:17 AM.
modest is offline   Reply With Quote
Old 07-07-2005, 06:44 AM   #13
Dan_T
Registered User
 
Join Date: Jul 2004
Location: Cornwall, UK.
Posts: 116
Thanks: 0
Thanked 0 Times in 0 Posts
Dan_T is on a distinguished road
Hi I am having a wee bit of trouble with executing the function:

ExportDatabaseObjects()

To me it looks fine but at this line.

For Each d In c.Documents

I get the error: Error 13 - Type Mismatch.

Up until this point all is fine with the world and the tables and queiries are beautifully exported to the location I have specified.

Any ideas?

Many Thanks

Dan.

I am using Access 2003 Win Xp Pro.

Last edited by Dan_T; 07-07-2005 at 06:51 AM.
Dan_T is offline   Reply With Quote
Old 07-07-2005, 07:21 AM   #14
ghudson
Newly Registered User
 
ghudson's Avatar
 
Join Date: Jun 2002
Location: USA
Posts: 6,199
Thanks: 1
Thanked 87 Times in 49 Posts
ghudson has a spectacular aura about ghudson has a spectacular aura about ghudson has a spectacular aura about
Not a clue for it works fine for me with Access 2003. Try just running the piece for just the modules since that is all you want. Or is that where it is bugging out?
__________________
.................................................. ......
Searching this forum or Microsoft.com or MSDN.com or Google is a great way to discover and learn the answers to your Access programming questions.

Well if it seems to be real, it's illusion...

I am using Access 2010 with Windows 7

The
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
function on this forum really does work.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.

.................................................. ......
ghudson is offline   Reply With Quote
Old 07-07-2005, 08:32 AM   #15
Dan_T
Registered User
 
Join Date: Jul 2004
Location: Cornwall, UK.
Posts: 116
Thanks: 0
Thanked 0 Times in 0 Posts
Dan_T is on a distinguished road
I've tried stepping through it and the parts for both forms and queries work fine. Errors occur for the steps for any part using the d variable for documents (Forms, reports, scripts). So I set a break point at the line:

Set c = db.Containers("Forms") 'Declared Dim c As Container

This sets C ok. Stepping through to the next line:

For Each d In c.Documents 'Declared Dim d As Document

The error occurs when this line is executed. When I check back after the error, hovering the mouse over the d variable I see, d = nothing. When I hover the mouse over the d variable on the following line:

If Left(d.Name, 1) <> "~" Then

I see, "d.name = <Object variable or With block variable not set.>

Thanks again for any help on this. I haven't changed any of the code other than to point to the folder I wish the .txt files to be created in, and obviously I have the DAO reference in place for DAO3.6.

Dan.


Last edited by Dan_T; 07-07-2005 at 09:08 AM.
Dan_T is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Using an imperfect text file as a table Steven Deetz Tables 4 08-02-2004 07:19 AM
Access to Text File - Export manianz General 4 06-04-2004 06:44 AM
How do I issue items from a multi-level inventory? ltl Forms 5 01-27-2004 12:32 PM




All times are GMT -8. The time now is 01:15 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World