[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Access 2010 macros to query table

Posted on 2011-05-05
17
Medium Priority
?
313 Views
Last Modified: 2012-05-11
I have been developing on Access 2003 for years but I'm having problems transitioning to Access 2010. I've been reading that we should use macros rather than vb code for security reasons.

I am trying to create a login form where the user enters their username and password then the app queries the user table to validate the data. This would've been so easy for me in 2003 but I'm stumped in 2010!

1. I cannot seem to find macro actions to do this.
2. Since I couldn't find macro actions, I tried to write vb code. When I create a button on a form, I cannot find a way to write/access vb code for the On Click event - something easily done in 2003 (Event Procedure).
3. I tried writing simple macros with the intent to use the Convert macro to vb but I cannot find that command as documented under the Database Tools, Macro tab. The only options I have are Visual Basic and Run Macro.

Help!
0
Comment
Question by:mph23
  • 8
  • 7
  • 2
17 Comments
 
LVL 21
ID: 35703207
In Access 2010 the convert Macro to VBA code does not work. Hopefully this will be fixed with SP1.

All the Acess guru I know still still write lots of VBA code in Access 2007 and 2010.

You can still create event procedures in Access 2007/2010. The default for the  control wizards is to make an embedded macro. To create the VBA code you can't use the wizards.

For whatever it worth: I still use lots of VBA code in Access 2007/2010.

Boyd aka HiTechCoach
Microsoft MVP - Access
0
 

Author Comment

by:mph23
ID: 35703369
So how do you get to the vb code from a button control in form layout view?

When I press the On Click event, it's blank (2003 used to give you Event Procedure option to get to the vb code).

If I press Visual Basic in the tool bar, it goes to visual basic, but I cannot find the form I have open or any of its controls.
0
 
LVL 21
ID: 35703652
I almost never use Layout view. I generally disable it.

In design view it appear to work the same as it always has.
In the property sheet, select the event and click the down arrow to select Event Procedure.

 In Design View
It works the same in Layout view
 Layout View
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 21

Assisted Solution

by:Boyd (HiTechCoach) Trimmell, Microsoft Access MVP
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 2000 total points
ID: 35703661
I assume you are NOT working with a web database and a web object. Is that correct?

Web Objects do not support VBA code only macros.

Boyd aka HiTechCoach http://www.hitechcoach.com
Microsoft MVP - Access
0
 

Author Comment

by:mph23
ID: 35703703
I started with a template. How can I tell if it's a web db and object. Maybe that's my prob.

I don't see design view. Only layout view.
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35704988
The template name says whether it is web or not.
And there is usually a Getting Started tab which says that it is a web databse, if it is one.
0
 

Author Comment

by:mph23
ID: 35708112
The template was the assets template and it doesn't have web in the name.
But I was playing around with Save and Publish so could I have inadvertently made it a web database?

Is there a way to check and change it back to be non-web?
0
 

Author Comment

by:mph23
ID: 35708296
I'm sorry, it DOES have web in its name.

How do I make it a non-web db?
0
 
LVL 77

Expert Comment

by:peter57r
ID: 35708330
I don't know of any way to do that.
See what Boyd says when he's around next.

0
 
LVL 21
ID: 35708563
<How do I make it a non-web db?>
The short answer is:  you can't go backwards.

Unfortunately at this time I have not been able to find any way to convert a web database to a desktop database. At least not googling/binging.

Now I am curious. It may be possible to save as text. Edit the object. Import it back in.  I will try it out now and let you know in a few minutes if that can be done.  

TIP: To see if it is a web object look at the icon in the Nav Pane. If it has a little global in the lower half then it is a web object.

Boyd aka HiTechCoach http://www.hitechcoach.com
Microsoft MVP - Access
0
 

Author Comment

by:mph23
ID: 35708721
Yes, they have the little blue icon.
Let me know how your test goes. What are you saving as text? Forms? All objects?

Thanks!
0
 
LVL 21
ID: 35708801
Well I tried backup up the object. editing it and restoring it.  I was able to get the object to no longer think it was a web object. The bad news is that not all the form works. There must be some other things that needed to be changed. It might work with some more time to figure out.
0
 
LVL 21

Accepted Solution

by:
Boyd (HiTechCoach) Trimmell, Microsoft Access MVP earned 2000 total points
ID: 35708942
FYI: Here is how I was backing up and restoring:

Backing up and Restoring Objects

Boyd aka HiTechCoach http://www.hitechcoach.com
Microsoft MVP - Access
0
 

Author Comment

by:mph23
ID: 35718614
How do I run the code from a web db to save the form:

Application.SaveAsText acForm, "Customers", "C:\backups\forms\Customers.txt"

I tried writing code in a module but I can't seem to run the code.
I tried adding a button to a form and embedding a macro with RunMacro, but runmacro doesn't recognize the macro in the module.

0
 
LVL 21
ID: 35720687
I ran my test SUB in a module from the VBE immediate window  (Ctrl-G).
0
 

Author Comment

by:mph23
ID: 35722582
Great idea! thanks
0
 

Author Closing Comment

by:mph23
ID: 35735703
Thanks
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question