Can anyone help me with a VBA code that will transfer a spreadsheet into a table in access but between the transfer, I wanted to have some kind of a process that will do insert into the tables based on conditions and will automatically generate an id in Access table and insert either "Regular" or "Temp" based on the Last Name wildcard condition.
Thank you in advance.
Example fields on excel file number 1(transfer #1)
LastName FirstName Address BirthofDate
Smith John LA July 6, 1982
Johnson Mike CA June 1, 2004
fields on excel file number 2(transfer #2)
LastName FirstName Address BirthofDate
Johnper Mike KY July 6, 1982
Kilroy Mel NE June 1, 2004
AFTER Transfer 1 and 2, the
Desired fields on Access should be below
ID LName FName Address BirthofDate Remark(note that if there is "John" in the last name in excel file)
1 Smith John LA July 6, 1981 Temp
2 Johnson Mike CA june 1, 2004 Regular
3 Johnper Mike KY July 6, 1982 Regular
4 Kilroy Mel NE June 1, 2004 Temp
This Question has been solved and asker verified All Experts Exchange premium technology solutions are available to subscription members.
Experts Exchange has been collecting answers to technology questions since 1996…3 million and counting! If you have a question, chances are we already have your answer.
If you can't find the exact answer you're looking for, ask our exclusive community of 50,000 experts. You’ll get a personalized answer from a trusted professional.
Thousands of free tech tips, tricks, how-to’s and tutorials are available in our peer reviewed articles section. See for yourself how smart our experts are, no login required.
Save solutions to your questions, answers you’ve discovered through searching plus helpful articles in your personal knowledgebase for easy future access.
Access the answers to your technology questions today.
30-day free trial. Register in 60 seconds.
Members of the expert community talk about why the experience at Experts Exchange is different than what you will find anywhere else.
Try it out and discover for yourself.
30-day free trial. Register in 60 seconds.
Join the community of experts here and help other tech pros by answering question in your area of expertise. You can earn FREE access to all Experts Exchange's premium features and resources.
You can run the update code right after you import the record.
Here are couple questions.
1. If you don't want to use auto number for you PK, what do you want to use then?
>>>note that if there is "John" in the last name in excel file
2. I still don't see it, but what is the criteria for the field 'Remark' to become "Temp" or "Reguler"?
blee17517
Thanks for the help blee 17517
For the first question, I would like to set the primary key in the table as text and The VBA code, if possible will assign incremental(autonumber to it)
For the second question
Let me change the excel file to make it clearer, For the condional for Code, if there is "Division" into the the Code, you set the Status Field in the Acces table as "Regular" , else "Temp"
first excel file (Transfer 1)
LastName FirstName Address BirthofDate Code
Smith John LA July 6, 1982 DivisionTent
Johnson Mike CA June 1, 2004 DivisionHen
fields on excel file number 2(transfer #2)
LastName FirstName Address BirthofDate Code
Johnper Mike KY July 6, 1982 DepartmentSeal
Kilroy Mel NE June 1, 2004 DivisionAlpha
AFTER Transfer 1 and 2, the
Desired fields on Access should be below
ID LName FName Address BirthofDate Status(note that if there is "John" in the last name in excel file)
1 Smith John LA July 6, 1981 Regular
2 Johnson Mike CA june 1, 2004 Regular
3 Johnper Mike KY July 6, 1982 Temp
4 Kilroy Mel NE June 1, 2004 Regular
blee 17517, you are right, I have spent an enourmous on figuring out a VBA code in the transfer process but there is no point And your first and second comment already answered the question, I am glad that you were such a big help . t For the informational purposes of others, Autonumber a s a primary key would work for this problem and having an Append statement like the one below would work as well..
But before you create a sql code.
1)Create a table that will store the imported records from excel named for example as tblExcelSource with prmary key as Autonumber
2)create a table that would be that actual table named for example tblActual with primary key As text.
3)The code that will meet the coditions if The Code has Division to it would be below:
INSERT INTO tblActual(ID, LName, FName, Address, BirthofDate, Status)
SELECT tblExcelSource.ID, tblExcelSource.LName, tblExcelSource.FName, tblExcelSource.Address, tblExcelSource.BirthofDate
FROM tblExcelSource
Business Accounts
Answer for Membership
by: blee17517Posted on 2005-07-13 at 17:54:35ID: 14436978
Can you import into the the access table then run an update sql?