[RESOLVED] Update 2 Access DBs with SQL

[VB4fun]

I have two Access DBs. One has all of the Customer Information (Sales DB)and resides on a machine without Access. The other contains a table with New Customer Information (NewInfo DB) which is emailed to the receipients frequently, in the same table format.

I would like a routine using SQL, to Update the information in the Sales DB,with information from the NewInfo DB.

I would also like a routine to Add New information into the Sales DB, with New records from the NewInfo DB.

I have the following so far:

Public Sub UpdateCustomerData()
    Dim SQL As String
    Dim CnnSales As ADODB.Connection
    Dim CnnNewInfo As ADODB.Connection
    
    Set CnnSales = New ADODB.Connection
    strConnectSales = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & App.Path & "\DB\Sales.mdb;" & _
        "Persist Security Info=True;"
    
    Set CnnNewInfo = New ADODB.Connection
    strConnectNewInfo = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & App.Path & "\DB\NewInfo.mdb;" & _
        "Persist Security Info=True;"
 
    CnnSales.Open strConnectSales
    CnnNewInfo.Open strConnectNewInfo
        
    SQL = "Insert into [tblCustomers] (Account,Name,Address) values ('1','2','3')"
    CnnSales.Execute SQL
 
End Sub

I am just stuck on how to write the WHERE portion of the SQL, refering to the second DB and table.

I think I can get the second routine, if I could just get over this hump.

Help?

[RhinoBull]

You can have a link table(s) in one db from another so you wouldn't have to connect to both - use the same connection to update each table (physical or linked).

[VB4fun]

Stupid question then,

How would I link the tables in the code?

[RhinoBull]

Have a look at this article directly from MSDN:

Working with Tables in MS Access

[VB4fun]

Linking in this ways seem strange to me.

Every so often, sometimes as often as weekly, the NewInfo DB is sent to the appropriate user. The user simply writes over the previous NewInfo DB and runs the Update/Add New routine.

The Users do not have Access loaded on the machines.

By linking in the way you suggested, it seems like a lot of overhead with the possibility of issues each time the DB is written over.

Isnt there a ways through the SQL command? I am not that up on SQL, so I am not sure where to head. I have done a ton of searching but cannot come up with the answer.

[RhinoBull]

... it seems like a lot of overhead with the possibility of issues each time the DB is written over....

In my opinion you will have much more overhead with 25 (or so) databases that are "sent to appropriate user" regardless of frequency.
Can't you have one database that is accessed by multiple simultaneous users?
If you develop in lan environment then place your db in shared folder and voila ... well not exactly as you would have to develop some multi user logic to lock records, etc...

[VB4fun]

I wasnt sure I needed to get into the whole scenario of the situation, as this is only a small part of what I am updating. However, here is an overview, remember I am trying to keep it brief for anyone reading this post.

The home office generates a DB file. In the DB file is customer information, (tblData.)

As often as necessary, determined by home office or user, the file is sent via Email (as stated in the first post) to the users machine (in most cases a laptop.)

The user selects Update/Add New routine (I am trying to finalize) and the Customer data is updated with any changes to phone, address, names, etc.

The next routine automatically adds sales/inventory data for a period of time specified. (In this routine I dont want to write over any data since there will be previous periods in the DB already)

Not every customer is in the NewInfo DB, since not all customers have changes.

There will be NEW customers in the NewInfo Db, so there is a need to INSERT INTO for these customers and associated information.

For the sake of discussion, lets say there are 45 users, most of which do not have access to the main server....nor really need it, with the exception of this quick update of information. So your last suggestion isnt feasible.

For each of them to load M$ Access just for the handling of this routine is rather overkill.

So, with that said, does anyone know of a way to do this with SQL or a way where I dont need to run through each record, using a recordset? (for the sake of it, lets say there are 1500 records)

[RhinoBull]

I see what you do and still say you need a central database...
For that I would have MS SQL Server or MS SQL Extress 2005 so it could be online. If your users have email then they have access to the internet.
With this in mind any of your users can easily connect to your main database and upload their new and/or modified records directly.
This way you wouldn't have to worry with emails, etc, etc, etc...

[VB4fun]

assuming the main database is online.

[RhinoBull]

What type database?
- if it is MS Access then it's very tricky and not very reliable (link in my signature)
- if it's SQL Server (and others) then check "connection strings" link in my signature as well.

All you have to do is let your client application connect to remote database and directly update it based on local data OR let them work directly if your users don't mind working on line every time.

[leinad31]

If I'm not mistaken, if you pass them DBF databases instead of access then you can treat that DBF file as a table so you will have

INSERT INTO Table FROM 'path of dbf file nested in either single or double quotes, forgot which'

Or it might be

INSERT INTO Table FROM SELECT (fieldslist) FROM 'path of dbf file nested in either single or double quotes, forgot which'

Not sure on the syntax... its been ages since I last used that method

[VB4fun]

Thanks, leinad31

Now we are getting somewhere. I have not worked with DBF files so let me see if I can get that to work.

I appreciate your help

[si_the_geek]

Here's an example of the Access version of the syntax that leinad31 was thinking of:

SELECT * FROM tblErrorLog in 'Q:\path\file.mdb'

However, a linked server as RhinoBull suggested could well be more appropriate, as you only need to use the link (and be online) when updating.

[VB4fun]

Si ! You da guru! (and leinad31 too...)

That is what I was looking for! Thanks!

I understand, if the situation is feasible, Rhinobull's suggestion might be more appropriate.....

However, the main server is not on the Internet and the remotes do not have all have access....some pick up email, some receive floppys (remember those?)....I know it sounds antiquated....but as a whole the system has been working fine for quite some time....and believe it or not, in this day and age, the main server does not have a reason to be online...

Thanks for they help, you are all great!

[VB4fun]

I cant seem to get the WHERE portion to work here. It keeps telling me I create duplicate keys if it proceeds if I dont have it.

When I do have the following, it tells me "no value given for one or more parameters" I have tried placing the table names before the brackets, but that did not work either.

Can you see where I am going wrong?

SQL = "INSERT INTO tblCustomers (Account) SELECT (CustomerNumber) FROM tblData in 'C:\MyPrograms\Db\Test.mdb' WHERE (Account)<>(CustomerNumber);"

[ganeshmoorthy]

no value given for one or more parameters

means you have specified a field name which does not exist in the table...try this

SQL = "INSERT INTO tblCustomers [Account] SELECT [CustomerNumber] FROM tblData in 'C:\MyPrograms\Db\Test.mdb' WHERE [Account]<>[CustomerNumber]"

and use '[ ]' instead of '( )'

[VB4fun]

I had brackets originally but,

When I try that....I get
Syntax error in INSERT INTO statement


am I missing something else?

[si_the_geek]

The syntax is generally OK, apart from having too many brackets. Try this:

SQL = "INSERT INTO tblCustomers (Account) SELECT CustomerNumber FROM tblData in 'C:\MyPrograms\Db\Test.mdb' WHERE Account<>CustomerNumber;"

..and if that doesn't work, try specifying the table names too (which ideally you should do anyway, but may not be supported):

SQL = "INSERT INTO tblCustomers (Account) SELECT CustomerNumber FROM tblData in 'C:\MyPrograms\Db\Test.mdb' WHERE tblCustomers.Account <> tblData.CustomerNumber;"

It may help to remove the semi-colon from the end of the SQL too.

[VB4fun]

<pulling hair out>
Doesnt matter which one I try, I get

"no value given for one or more parameters"

[si_the_geek]

Well that's a better error, and has made me notice something I shouldn't have missed before... you can't do a <> because there is no record from tblCustomers to check against.

What you need to do is add a sub-query, and use Not In, eg:

INSERT INTO tblCustomers (Account) 
  SELECT CustomerNumber 
  FROM tblData in 'C:\MyPrograms\Db\Test.mdb' 
  WHERE tblData.CustomerNumber NOT IN (
      SELECT Account
      FROM tblCustomers)

[VB4fun]

Si,

YOU ARE AWESOME!!!!!!!!!!!!!!

Thanks,

Works like a charm.