Link to home
Start Free TrialLog in
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
ASKER CERTIFIED SOLUTION
Avatar of peter57r
peter57r
Flag of United Kingdom of Great Britain and Northern Ireland image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of MECR123
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


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.
Avatar of 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 ?)    
Avatar of MECR123

ASKER

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