Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

DSN Less connection in VBS to and Access database. Error in Set Conn...

Posted on 2013-11-11
12
501 Views
Last Modified: 2013-11-12
In the code below I get an error at Set conn1
Either server is undefined or object expected.
What am I doing wrong?


OPTION EXPLICIT

DIM Conn1, rs1, cst, strSQL1, myMail, body

' OPEN a OLE DB CONNECTION
      Set conn1 = server.CreateObject("ADODB.Connection")
      cst = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:\Users\Administrator\Desktop\ScheduledDBs\OUTRIDsXpert.accdb;"
      conn1.open cst
      
      'Create a Record Set with the Conn from above, then Set SQL, and then execute
      Set rs1 = Server.CreateObject("ADODB.Recordset")      
      strSQL1 = "select *  from Qry_CountRIDS-ByRSE"      
      rs1.Open strSQL1, conn1,3,3
0
Comment
Question by:Mswetsky
  • 6
  • 4
  • 2
12 Comments
 
LVL 9

Expert Comment

by:WebDevEM
ID: 39639815
Is that code running in a VBS file on your computer, or as an ASP page on a web server?  Its been a while since I've dealt with VBS files locally, but I think the "server." is only required if you're running in IIS.  If you're running it from a .VBS file try dropping that and using
Set conn1 = CreateObject("ADODB.Connection")

Open in new window

as shown here.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 39639869
Yes I am trying to use a vbs to read from the access db and mail a note with a table.
I changed to that but get a "provider can't be found" 800A0E7A
0
 
LVL 9

Expert Comment

by:WebDevEM
ID: 39639943
I remember that driving me nuts when I was working on Access files - Common wisdom is to make sure you're on the latest version of your programs, drivers, etc, but at some point Microsoft stopped shipping JET with their MDAC drivers.  So you need extra files that aren't installed with the latest version.

There's a good article here that may help get it installed.
0
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
LVL 1

Author Comment

by:Mswetsky
ID: 39639986
Thank you for your patience.
It seems that accdb uses a new oledb string.
http://www.connectionstrings.com/access/ 
I tried the sample here but am still unable to get it to pass that line.
Tomorrow is a new day and I'll try then.
0
 
LVL 65

Assisted Solution

by:RobSampson
RobSampson earned 500 total points
ID: 39640539
As you saw, use Microsoft.ACE.OLEDB.12.0

This should work
OPTION EXPLICIT

DIM Conn1, rs1, cst, strSQL1, myMail, body

' OPEN a OLE DB CONNECTION
      Set conn1 = CreateObject("ADODB.Connection")
      cst = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Users\Administrator\Desktop\ScheduledDBs\OUTRIDsXpert.accdb;Persist Security Info=False;"
      conn1.open cst
      
      'Create a Record Set with the Conn from above, then Set SQL, and then execute
      Set rs1 = CreateObject("ADODB.Recordset")      
      strSQL1 = "select *  from Qry_CountRIDS-ByRSE"      
      rs1.Open strSQL1, conn1,3,3 

Open in new window


Regards,

Rob.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 39641398
Thank you for your suggestions.
Just FYI, Running on Server 2008R2. (Don't know if that matters)
I have attached images of the current code and error.Code as suggestedError message
0
 
LVL 65

Accepted Solution

by:
RobSampson earned 500 total points
ID: 39641449
Try installing the Access database engine from here:
http://www.microsoft.com/en-au/download/details.aspx?id=13255
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 39641935
I am working on a production Server and would like to avoid reinstalling Access.
You and I know of all the permissions and stuff can get broken
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39642366
Yes, but for the code to work you need a driver installed. Maybe you can build a "service" server that can run tasks like this.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 39642526
Thanks again Rob And Ed,
I believe I understand now. Sometimes I get so caught up I forget to look.

I will try and find another way to install the new drivers, or attempt to re-install Access after making a server copy during off work hours.

I really do appreciate your patience and the chance to learn.
0
 
LVL 65

Expert Comment

by:RobSampson
ID: 39642806
Sure. Just keep in mind it's not the full Access product. It's just a database engine driver to facilitate data connections for requirements like this.  If you're running virtual machines, take a snapshot before installing it, just in case anything goes wrong, but it shouldn't be a problem.
0
 
LVL 1

Author Comment

by:Mswetsky
ID: 39643016
Oh I didn't realize that!  
Sort of like MDAC

Thank you for the clarification, I will feel much more confident!

It is a VM and that was my intent with a server copy.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Help with query 3 31
Sudden decrease in performance when updating mysql using classic asp 6 28
Convert .csv to tab delimited text 11 33
send email part1 9 26
This script will sweep a range of IP addresses (class c only, 255.255.255.0) and report to a log the version of office installed. What it does: 1.)      Creates log file in the directory the script is run from (if it doesn't already exist) 2.)      Sweep…
This demonstration started out as a follow up to some recently posted questions on the subject of logging in: http://www.experts-exchange.com/Programming/Languages/Scripting/JavaScript/Q_28634665.html and http://www.experts-exchange.com/Programming/…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question