Friday, December 19, 2008

Linking MS Access to SQL Server - labels

I had a label generator plugin for MS Word... from Avery Denison to print my labels. The data for the names/addresses resides in a SQL Server Database.

Avery Dennison can import from a Word doc, a Spreadsheet, or from an Access db.

I didnt want to download a spreadsheet all the time before importing, so I decided to use Access as a front end and link tables.... Theoretically it was a simple process..

Here is the article I found to do this:

It worked fine - until I closed the file and tried to re-open. Then I had to enter the password again.

Unfortunately, I had problems holding on the the username/password in the ODBC connection I made.... The following article explains why... The odbc connection to Sql Server connection kept defaulting to being a "trusted" connection instead of using the Sql Server username/password that I gave it. Thank you Bill Gates!

I tried applying the latest patch - no luck. Later I will revisit this...


I just saw I knew MS Word had some sort of feature like this - just couldnt figure out where. But like a fool, I tried using Avery Dennisons software.

No luck. I had problems connecting to SQL Server 2005 db. Probably the same problem as above. I am connected to it easily from my web server. Only my desktop has issues.


Ok.... Another angle:

- building MS Word on web server using C# and then downloading.

Looks like it may be possible to do what I want directly from the web server. I may need some sort of office components installed.. not sure.


Another angle. I am going to try to get MS Access to pull data from my database.
Then I will use MS Access to run the label writers...

YES!! This works if you have the data in MS Access. I was able to print the labels successfully! I even got MS Access to import the data from Excel. I just had to remove all the formatting done by my Excel generation code.

NEXT STEP: use vba to pull data into MS Access from my prospect management system. That would be very useful... if I had a canned tool to build mailing labels. I will need a way to pull the data from a web service or similar... I have some ideas that should work perfectly. Only constraint will be that the user has MS Access. Using data from:

No comments:

Post a Comment