access vba Access2010 table to sql server table

Is there a vba to transfer data from an access table to SQL server table? Assume both the tables in the access and sql server have the same name of “RandomTable”

The columns are below

Name | Phone
Ryan R |345234444

How do I transdfer the data row from access to sql server…and I want to use vba , not linked table

Thank you
RayneAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

RayneAuthor Commented:
thank you
0
Ryan ChongCommented:
How frequent is the data to be transferred over to MS SQL? Is this on ad-hoc basis?

How much data to be transferred over to MS SQL?

When you mentioned transfer, is that mean inserting records from Access to MS SQL?
0
Gustav BrockCIOCommented:
VBA has to link the SQL table somehow, so why not create a link vai ODBC or ADO and run an update query?

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Vitor MontalvãoMSSQL Senior EngineerCommented:
Do you really need a code to do that?
You can do that from the SQL Server side. You just need to open the SQL Server Management Studio, right-click on the target database, select Tasks / Import Data... and then follow the wizard.
TaskMenu.PNG
0
RayneAuthor Commented:
Hello Ryan,
How frequent is the data to be transferred over to MS SQL? >> Is this on ad-hoc basis? This will be triggered on a ad hoc basis whenever someone updates the access database

How much data to be transferred over to MS SQL? About 3000 rows

When you mentioned transfer, is that mean inserting records from Access to MS SQL? There will be a table in sql server, so the transfer will just add rows to the ms server table



Gustav,
VBA has to link the SQL table somehow, so why not create a link vai ODBC or ADO and run an update query? > how do I do it?


Victor – regarding your question – the access lives in SharePoint folder, so this option will not work as SharePoint folder links have their own issues. I want the access users to trigeer this data import from access to server.
0
Gustav BrockCIOCommented:
Go to menu External Data, select More, select SharePoint.
Follow the next steps selecting your table.

/gustav
0
RayneAuthor Commented:
Gustav,

my access sits in sharepoint and its trying to access the sql server table , i don't understand if your suggestion makes sense
0
RayneAuthor Commented:
I was able to link the sql server table in access
0
RayneAuthor Commented:
how do i delete the data rows fro that linked sql server table from within access? its not letting me in accesssql...can you help?
0
Gustav BrockCIOCommented:
My guess would be that the right to delete is not granted.
However, I'm not a SharePoint admin so I can't tell how to correct it.

/gustav
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.