Free Lessons
Courses
Seminars
TechHelp
Fast Tips
Templates
Topic Index
Forum
ABCD
 
Home   Courses   Index   Templates   Seminars   TechHelp   Forums   Help   Contact   Join   Order   Logon  
 
Home > TechHelp > Directory > Access > Track Changes < Form Backgrounds | Command Line >
Back to Track Changes    Comments List
Wildcard in newer versions Upload Images   Link   Email  
Sharon Splinter 
Hi, I followed to the T, the directions given by this video and got error messages which I didn't understand.  I looked them up on the Internet, and found that the Wildcard, "*" wouldn't work in the example given in this video.  Once I wrote out each field in the SQL statement instead of using the Wildcard, then the SQL statement worked.  For large Tables, writing out each field, is a real pain.  I hope there is an alternative.
Adam Schwanz 
What did you put and what was the error? I use the * wildcard daily it definitely still works, i haven't seen this video in ages but i don't see why there would be anything done different in it. Now if your in sql server or something you may need to use %.
Kevin Yip 
HI Sharon, you can only use:

     INSERT INTO Table1 SELECT * FROM Table2

only if *all* of the column names of Table1 and *all* of those in Table2 match exactly.

To avoid typing lots of field names (and to avoid typing errors), use a saved query instead of an SQL statement in VBA.
Sharon Splinter 
Thank you Adam.  I'll try that.

Kevin, I'd attach screen shots if I could to help explain the situation.  Anyway, here's what I typed:  DoCmd.RunSQL "INSERT INTO CustomerChangeLogT SELECT * FROM CustomerT " & _ "WHERE
Sharon Splinter 
Kevin, accidentally hit Submit.  Continuing...
"WHERE CustomerID=" & CustomerID  

There error code I get is a Runtime error '3825' which basically says I can't use the *.  When I type out each field, the SQL statement works.
Kevin Yip 
But do the two tables have exactly the same field names?  Again, all of their field names must match.  If one table has fields A, B, and C, and the other only has A and B, it won't work.  The two tables must have identical structure for that to work.
Sharon Splinter 
I made a copy of the CustomerT table and only used it's structure, so they're the same.  When I typed the fields individually, I used the tables to get my information.
Kevin Robertson 
Is CustomerID an AutoNumber in the CustomerChangeLogT? Try changing it to a Number.
Sharon Splinter 
I just now double checked, and I did change it to an Number per the Tutorial.  I played with it again today, and get the same error message as soon as I change it to a wildcard.
Adam Schwanz 
Can you upload some images? That button next to the reply box.  Or Image Uploads
Kevin Yip 
Hi Sharon, what is the complete message of the error?  Does it say something about "multivalue fields"?  If either table has multivalue field(s), INSERT INTO won't work.
Sharon Splinter 
Yes that's the one.
Kevin Yip 
Then you must omit the multivalue field in the "SELECT" portion of your query, and therefore you can't use "*" and must list all fields (except the MV field) in the SELECT clause.  Richard has a section on this site called "Evil Access Stuff" which includes multivalue fields, and this is one reason.  Having to list all field names is a relative minor thing in this instance.  But there are other evils of multivalue fields as I and others can personally attest.
Sharon Splinter 
Thank you for looking into this for me.  I'll check out "Evil Access Stuf"f.  Interesting name...  I wanted to use the lesson in my own Access database to record changes I made, but it doesn't do that at the level I need.  I change formatting, add tables, etc...    Although, when the SQL script does work, it asks the user if they want to append the change.  Sounds dangerous.  I bet there is a  way around that.  Again, thank you.
Kevin Robertson 
Yeah. A couple of ways:

Docmd.SetWarnings False
DoCmd.RunSQL "---------"
Docmd.SetWarnings True


OR

Instead of DoCmd.RunSQLL use CurrentDB.Execute

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Track Changes.
 

 
 
 

The following is a paid advertisement
Computer Learning Zone is not responsible for any content shown or offers made by these ads.
 

Learn
 
Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Seminars
More...
Customers
 
Login
My Account
My Courses
Lost Password
Memberships
Student Databases
Change Email
Info
 
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Code Vault
Collapse Menus
Help
 
Customer Support
Web Site Tour
FAQs
TechHelp
Consulting Services
About
 
Background
Testimonials
Jobs
Affiliate Program
Richard Rost
Free Lessons
Mailing List
PCResale.NET
Order
 
Video Tutorials
Handbooks
Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Shipping
Terms of Sale
Contact
 
Contact Info
Support Policy
Mailing Address
Phone Number
Fax Number
Course Survey
Email Richard
[email protected]
Blog RSS Feed    YouTube Channel

LinkedIn
Copyright 2024 by Computer Learning Zone, Amicron, and Richard Rost. All Rights Reserved. Current Time: 3/28/2024 6:55:33 AM. PLT: 0s