Avatar of MECR123
MECR123
 asked on

Read and SQL table and update data into an Access table

Hi

I have an access database Created  - I am using Access 2007 and I have saved the database to 2003 as the users are on 2003
I have a main table in this Access database that I can maintain (Add/update/delete) records

I want now to be able to connect my access database to an SQL database – this SQL database is accessible through a user ID and password

In my access database we will call the table – tableACC
This has 5 fields

In my SQL database we will call the table – tableSQL
This table has 30 fields – I only want to take certain fields from this table

I want a create a form with a ‘Button’ which will invoke the VB code to do the following

I want to Select records from TableSQL that are Open (TableSQL.Open = yes) and Created between 2 dates  (TableSQL,CreatedDate Between Date1 and Date2) – These dates must be entered by the user (these dates are parameters) And only select records that are not already on the tableACC.

For all the records that are selected I want to add these into the Access table TableACC.

In summary
I have not written VB code before – I would like to know how to write this code – how to be able to call the code from a ‘button’  - how to input the dates as parameters – how to open the SQL database with a user ID and password  - how to read the SQL table – how to write to the Access table

Thanks very much for your help

Regards
Mike
Microsoft AccessMicrosoft SQL Server 2005

Avatar of undefined
Last Comment
MECR123

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
peter57r

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
MECR123

ASKER
hi Peter

thanks for that - I did not know you could do that - I have tried this and I can link the tables

It is a little more complicated as I look into it more (sorry I did not notice this before)

One of the values that I have to add to TableACC is a value that must be calculated by summing up the lines - I have TableSQLHeader and TableSQLLines - I wish to append 1 line to TableACC for each tablesqlheader but I must sum up a qty * value from the tableSQLLines.

also each time I am asked for the SQL user name and password - The user does not have access to these - is there a way to store them so that I as developer only enters them once at design stage


peter57r

So I'm assuming you are now using two tables from the SQLServer db?

Create a query on the detail table to produce a sum of the qty*price for each order(I'm guessing)(qrySum)

Create an append query using the header table and qrysum linked on orderid, and set all your selection criteria as required, and choose your access table as the target.

As for your authentication, I don't think I understand the idea of users not knowing their own login details.
But when you link the sqlserver tables you have the option of saving the password you used to create the link.
MECR123

ASKER
Hi peter
Yes - I managed to create the query in SQl design and link the tables and sum the values I require

as for the login - the tables are on our SQL production database so the users will not have access to the SQL password  - How do I save this when I link the tables (is it use trusted connection ?)    
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
MECR123

ASKER
the solution pointed me in the right direction so I was able to finish out what I was trying to do