Update mdb from SQL select 1

[SuperComputing]

I need to pull several columns from a table on a SQL server and then populate a Access mdb with the data.

I have searched for software, but can only find apps that convert mdb to sql, the opposite of what I need.

I've tried an asp page and can sucessfully pull the data that I need. I can even create the mdb file, but I cant 'push' the data into it. Even if I had to use an existing, empty mdb as a template, that would be fine.

Here's what I have so far:

<%
Dim CONN_STRING, filepath
CONN_STRING = "DSN=CPDATA;"
filepath  = "c:\inetpub\[URL unfurl="true"]wwwroot\cpinventory\files\"[/URL]
%>

<%
Set connSQL = Server.CreateObject("ADODB.Connection")
connSQL.open = CONN_STRING
strQuerySQL = "SELECT ItemNumber, Description1, Description2, Attribute1 AS Color, Attribute3 AS Size, Weight, Price1 AS Price, QtyOnHand - QtyCommitted AS Qty FROM Items WHERE Ecommerce = 'Y' AND Price1 > '0' ORDER BY ItemNumber"
Set rsSQL = connSQL.Execute(strQuerySQL)
%>

<%
set connMDB=Server.CreateObject("ADODB.Connection")
connMDB.Provider="Microsoft.Jet.OLEDB.4.0"
connMDB.Open filepath & "onlineinv.mdb" 
set rsMDB=Server.CreateObject("ADODB.Recordset")
rsMDB.open "SELECT * FROM inv WHERE ItemNumber='" & ItemNum & "'",connMDB

%>
<%
  sql="UPDATE inv SET "
  sql=sql & "ItemNumber='" & Request.Form("ItemNumber") & "',"
  sql=sql & "Description1='" & Request.Form("Description1") & "',"
  sql=sql & "Description2='" & Request.Form("Description2") & "',"
  sql=sql & "Color='" & Request.Form("Color") & "',"
  sql=sql & "Size='" & Request.Form("Size") & "',"
  sql=sql & "Weight='" & Request.Form("Weight") & "'"
  sql=sql & "Price='" & Request.Form("Price") & "'"
  sql=sql & "Qty='" & Request.Form("Qty") & "'"
  sql=sql & " WHERE ItemNumber='" & ItemNum & "'"
  on error resume next
  conn.Execute sql
%>

<%
connSQL.close
Set connSQL = Nothing
Set rsSQL = Nothing
connMDB.close
Set connMDB = Nothing
Set rsMDB = Nothing
%>

I know the 'Request.Form' lines aren't right, but I was trying it as an asp form. I just don't know how to make the page auto-populate the mdb when the page loads.

Anyone help me 'fill in the blanks?'

 

[worldwise]

First of all, do you have to do this as a web app? Could you just save the results of your SQL query as an excel spreadsheet and then load your Access database from that excel sheet?

If you need it to load from an ASP page then it could be kinda slow depending on the number of records you're talking about.

Basically you could loop through your rsSQL recordset and run that UPDATE code over and over for each record.

does that make sense?

 

[Chopstik]

I'm with worldwise on this about using ASP to accomplish what you want. I'm somewhat confused why you simply don't link or import the data you need from your SQL Server into MS Access. This is a fairly simple process and would be much quicker and easier than anything you can do with ASP.

If you're not sure what I'm referring to, then create a new table in Access and, when prompted, either link or import it from another source. At the next window, choose the ODBC data type and then find your SQL Server from there. The rest of the process should be pretty self explanatory.

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[SuperComputing]

Good points, but I need to give you the overall picture. I have my reasons for wanting to do it this way. I have a remotely hosted asp based website that reads inventory data from a mdb file. This file has to be updated daily. I would use a linked access file, but it can't link through the internet to the server inside my network. And I can't find a way to force it to 'retain' the query in a table and keep it as I ftp it to the server. As for a csv, that is fine too, but exporting a csv, importing to mdb, and ftp that file up is not a chore that I want to do everyday. I need an automated solution. I have the automated ftp part working, now I just need my access table generated and I'm done.

Any ideas?

 

[Chopstik]

Ok, I think I understand why you want to do it this way. There may be some other (more efficient) alternatives, but none that come immediately to mind.

However, if you want to use ASP, it looks like you're part way there. You can pull back the data as you've done from SQL Server. From there, you can then run a loop for each record returned to then create and execute an INSERT statement into your .mdb file. THe problem is that this is terribly inefficient and may take quite a while, depending on how many records you have.

Another possible thought is to see if there is some way to write a single SQL statement that will pull the data from one database (your SQL Server db) as part of an INSERT statement into your Access db. I know this would be preferable and I've done it when working with just one database, but not with multiple db's and not when they db types were different. Perhaps someone can offer additional insight into that particular method to see if it's possible.

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[gmmastros]

Hold on a minute. I'm beginning to understand (scary huh?).

You have a SQL Server database local.
You have a remotely hosted site that uses an Access database.
You want to export data from the local SQL Server database to an Access database daily, then FTP the file to the remote host.

Do I understand this correctly?

My recommendation would be this...

1. Create an access database locally that has the correct table structure.
2. Using SQL Server, create a linked server to this local Access database.
3. Periodically (daily?) copy the data from SQL Server to the local Access database.
4. Then FTP the Access file to your remote host.

Creating a linked server is SQL Server is easy.

Open Enterprise Manager
Drill down to your server.
expand 'Security'
Right click 'Linked Servers' -> New Linked Server

When selecting the provider, make sure you choose 'Microsoft Jet 4.0 OLE DB Provider'.

Using a linked server is even easier. Just use the 4 part naming convention for your tables. Like this...

Truncate Table LinkedServerName.DatabaseName.dbo.TableName

Insert Into LinkedServerName.DatabaseName.dbo.TableName(Col1, Col2, Col3)
Select Col1, Col2, Col3
From   SQLServerTableName
Where  Blah....

Once you get the queries right to populate the Access database, it's pretty simple to schedule a job to accomplish this.

Make sense?

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[Chopstik]

I knew that you were going to show up sooner or later on this, George... ;-)

Actually, I like that idea... I hadn't thought of it that way since I actually do it the other way around, but that would make it much easier. I can definitely see some good uses for this... Thanks!

------------------------------------------------------------------------------------------------------------------------
"As for the bureacratic, politically-correct, mollycoddling, asinine, Romper Room antics of...clients and management, just read up on Dilbert. It's not funny - it's a training manual."
- Mike

 

[SuperComputing]

You do understand me quite correctly, and where the idea sounds great, there is one big problem for me. I neglected to mention, out of embarrassment actually, that we are running pervasive v8. So, I can get just about any query statement to work, but have NONE of the management tools avail to MSSQL admins.

I am not terribly concerned with efficiency, my test with 500 records comes back in maybe a second, and the largest it may ever be is double that.

Great ideas all! And I thank you for putting your brains to work, but seems that I am stuck with the long way through this.

So, I have the SELECT statement that pulls the correct data.
I have a local, properly formatted blank database.
Can anyone help me with a loop that will INSERT the SQL rs into the mdb database?

 

[worldwise]

using what you started in your first post:

Do Until rsSQL.EOF
sql = ""
sql = "INSERT INTO inv (col1,col2,col3,etc...) "
sql = sql & "VALUES('" & rsSQL("ItemNumber") & "','"
sql = sql & rsSQL("Description1") & "','"
.
.
.
conn.Execute sql


rsSQL.MoveNext
Loop


That is probably the most basic implementation. Assuming that you are going to keep the same Primary Key in both databases, you are going to run into problems when you run this more than once. (It will try to insert values that you have already inserted and you will get a duplicate primary key error)

To avoid this, you should first select all your primary keys from the Access database and when you query the SQL database only bring back "new" items.

Another suggestion is that if you go with the on error resume next implementation you should keep a counter so that you can see how many records you successfully moved over. Better yet, if you run into an error you should write down the ItemNumber onto the page so you can see which ones had problems.

 

[SuperComputing]

Got it funtional, thanks for all of your help, the syntax above was exactly what I was needing. As for efficiency, it pulls my test 395 items and writes them in less than 2 seconds. Now for a little error checking and it will be live. Thanks again!!!