asked on
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
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
DatabasesMicrosoft SQL ServerSQL
Last Comment
MECR123
ASKER CERTIFIED SOLUTION
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
ASKER
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
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
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.
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.
ASKER
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
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
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.
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.
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?
ASKER
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.
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.
The use the linked table as source in a normal update query that will update the Access table.