Access VBA code to retrieve data from SQL server

Hi

I am trying to create a shipment database in Access. I wish to create an Access database that will  read a table from an SQL server based on a date and a courier name parameters and populate this data into an Access Table.

I will have an access form where the user will enter a date and courier name. They then will have a ‘button’ called ‘Get Data’. When they press this button I would like the Access system to go and retrieve the data from an SQL database passing the 2 parameters.  They will retrieve the fields (Shipment Date, Courier Name, sales order number and Shipped Qty).

The data should then be updated into a Access Table.  It should do a check to see does the record exist already in Access. (if the shipment date + Courier Name + sales order number already exist in the Access database then skip update into the Access database and move to next record).

What would be the Access Code that I will need to create the connection to SQL, Read the Data from SQL passing the parameters and then updating the records into the Access Table.

Regards
Mike
MECR123Asked:
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.

Gustav BrockCIOCommented:
Link the SQL Server table via ODBC in Access as a linked table.

The use the linked table as source in a normal update query that will update the Access table.
Dale FyeOwner, Developing Solutions LLCCommented:
You could use my Linked Table Manager (LTM) add-in.

Read about it and download from my article

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
MECR123Author Commented:
hi
The LTm looks very good - I have not tried it before and I will have a look into this. Thanks very much for the information

But for this project I am doing I would like to write the visual basic to connect via ODBC link to my SQL - read the data and update into an access table.

thanks
M
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

Dale FyeOwner, Developing Solutions LLCCommented:
So, you don't simply want to link the SQL Server tables to Access and use them locally?

Can you explain why?  One of the huge advantages of Access, over other development platforms, is that all of the overhead to link the data input forms and the actual data has already been created for you.  It sounds like you want to retrieve the data from SQL, then push it into the controls on your form, and then when this process is done, you want to push the data back out to SQL Server via another SQL command.  Access will handle all of that for you if you use linked tables.
MECR123Author Commented:
hi Dale

Yes you are correct - I do want to read the data from SQL tables and then manipulate it in Access. I won't be putting it back into SQL.  I will try out your LTM. Is it all self explanationary or do yo have a guide on how to get started and use it.
Many thanks
Mike
Dale FyeOwner, Developing Solutions LLCCommented:
There is a help (?) button in the upper right corner which allows you to browse through various features of the LTM, and a Print button on that form which allows you to print it all out in a help document.

I'm working on a couple of videos that will provide audio/visual examples.

But if you need to ask questions, just post a comment on the articles page and I'll respond there so that others can take advantage of any questions you have.
Dale FyeOwner, Developing Solutions LLCCommented:
Could you explain more about what you are trying to accomplish?  Most developers simply link the tables from SQL Server into their Access database and work with the data from there.  Are you going to be saving the data you are retrieving from SQL Server in your local Access database, or are you using this more as a reporting tool?
MECR123Author Commented:
Hi

On our ERP system we create deliveries throughout the day - This can be anything to to 200 deliveries per day and these are then collected by various couries and distributed to the customers.

I would like to download the following into Access from our ERP System
Courier ID, Delivery date, Delivery Note number, Customer name, Customer address,

I will have a couple of new columns in Access that the user can key a quantity into - field name will be ( Box, Carton, Pallet etc)

so a record on the Access database will look  something like
Courier ID, Delivery date, delivery Note Number, Cust Name, Cust address, Box, Carton, Pallet
Courier1, 06/12/2018, DlyNote01, Joe Bloggs,Address of cust, 1,0,0
Courier1, 06/12/2018, DlyNote02, Joe Bloggs,Address of cust, 0,2,0
Courier2, 06/12/2018, DlyNote03, Joe Bloggs,Address of cust, 1,0,0

 
When they have updated Access I would like the user to have an option to enter a courier ID and a date and depending on the courier selected a .xls or a .csv is created and attached to an email for sending to the courier company.

in summary my plan was ==> get the user to retrieve the data from the ERP system passing courier ID and a date, make their changes in  ACCESS and then save to .xls or csv and send email.  The delivery files I am taking the data from have a large number of records so that is why I wanted to retrieve only the records on a daily basis.  





I want to build an Access database so that the user can view these deliveries in Access, The user will be able to add  
Curently I download these deliveries into Excel and



I am trying to create a shipment database in Access. I wish to create an Access database that will  read a table from an SQL server based on a date and a courier name parameters and populate this data into an Access Table.

 I will have an access form where the user will enter a date and courier name. They then will have a ‘button’ called ‘Get Data’. When they press this button I would like the Access system to go and retrieve the data from an SQL database passing the 2 parameters.  They will retrieve the fields (Shipment Date, Courier Name, sales order number and Shipped Qty).

 The data should then be updated into a Access Table.  It should do a check to see does the record exist already in Access. (if the shipment date + Courier Name + sales order number already exist in the Access database then skip update into the Access database and move to next record).

 What would be the Access Code that I will need to create the connection to SQL, Read the Data from SQL passing the parameters and then updating the records into the Access Table.
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
Databases

From novice to tech pro — start learning today.