Using Access Tables with VB 2010

Question

Would someone please be kind enough to give me the exact code I would need, using Visual Basic 2010, to   1.  link to a table in an Access 2010 database

          2.  extract a field from a specific record in the table, using a key

          3.  link to a second table in the same database

          4.  add a record to the second table, containing the data from the first table.

 

I have tried SQL, LINQ and OleDb and I don’t seem to be able to make this work.

 

To make things clearer:  The database is called DCdatabase.accdb

                     Table 1 is DCPrime

                     Table 2 is Xref

                     The search key is held in a string variable called f_AccessKey

                     The matching field in DCPrime is AccessCode

                     The field to be extracted and updated into Xref is SysKey

 

Any help would be greatly appreciated.

---

John Daley

Answer 1

Hi DonnyJay,

Thanks for your post!

Please kindly read the following links regarding how to Select/Insert/Update/Filter records.

How do I search and filter records in the database

http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/9b2ad06e-de12-4c52-9c29-7507a824153e

How do I make a basic Data Access application
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/c27538e2-44d7-4cb9-9141-ed7ee733bf80

Save/load a data
http://social.msdn.microsoft.com/Forums/en-CA/vblanguage/thread/7ba999a4-9800-402b-b6e0-58455778f85a

Loop through datagridview by bands and insert records to database
http://social.msdn.microsoft.com/Forums/en-US/vbgeneral/thread/2e1fbb63-37f5-4e1f-9411-93f1917b23b3

Hope this information is helpful.

Best Regards,

---

Kee Poppy [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 2

Too many questions in one post. Narrow it down to a single topic and more people are likely to respond.

---

Paul ~~~~ Microsoft MVP (Visual Basic)

Answer 3

You can accomplish this with a simple T-SQL statement...

UPDATE d
SET d.SysKey = x.SysKey
FROM DCPrime d
JOIN Xref x
ON d.AccessKey = x.AccessKey
WHERE d.MyField = 'Something'

Adam

---

Ctrl+Z

Answer 4

Hi John,

Have you solved this issue?

In my first post, I shared some resources that can help you to create an Access Database Application with the features of Insert/Select/Filter/Update.

However, I am not sure if it can help you to understand the logic of using SQL, LINQ and OleDb together. If you could not work it out, do you mind sharing the project you have done so far so that we can get the issues you have and help you to debug?

 

Best Regards,

 

 

---

Kee Poppy [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 5

Hi, Kee

My apologies, but I have only now been able to check replies to my post.

I will go to the links that you suggested in your first post, after which, judging by the struggle I have had up to now, I would like to take up your offer of taking a look at my project.

I don't seem to see a way to upload code.  Perhaps you would advise me on this?

Regadrs.

---

John Daley

Answer 6

Hi John,

Sorry for my late replying!

You can upload your project to the SkyDrive

 

Best Regards,

---

Kee Poppy [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 7

Hi, Kee:

I uploaded the project on SkyDrive.  I hope you can find it because I had no idea what I was doing!  I set up a folder called VB Uploads which I shared with everybody.  Please let me know if there is anything else I should do.

I have checked "Alert me when someone responds to this post"  but I never receive an email when someone does so I will check back here regularly.

 

Thanks.

 

---

John Daley

Answer 8

Hi John,

Could you please tell the URL of your project folder on SkyDrive? Also, It would be better if you open up a new thread, because the age of this thread is very long and its status is closed. So, please open up a new thread and paste the project URL inside, thus, more members will involve in to efficiently help solving the problems.

For the Alert issue, what email are you using for it? You better use Hotmail since the activation email may be filtered by other email system.

Thanks,

---

Kee Poppy [MSFT]
MSDN Community Support | Feedback to us
Get or Request Code Sample from Microsoft
Please remember to mark the replies as answers if they help and unmark them if they provide no help.

Answer 9

Kee:

In case you do check back here, I have started a new thread, as requuested, "Access Tables in VB 2010" in which I included the URL to the project files.

---

John Daley