• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

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

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
Mitch Swetsky
Asked:
Mitch Swetsky
  • 6
  • 4
  • 2
2 Solutions
 
WebDevEMCommented:
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
 
Mitch SwetskyBusiness AnalystAuthor Commented:
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
 
WebDevEMCommented:
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
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
Mitch SwetskyBusiness AnalystAuthor Commented:
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
 
RobSampsonCommented:
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
 
Mitch SwetskyBusiness AnalystAuthor Commented:
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
 
RobSampsonCommented:
Try installing the Access database engine from here:
http://www.microsoft.com/en-au/download/details.aspx?id=13255
0
 
Mitch SwetskyBusiness AnalystAuthor Commented:
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
 
RobSampsonCommented:
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
 
Mitch SwetskyBusiness AnalystAuthor Commented:
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
 
RobSampsonCommented:
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
 
Mitch SwetskyBusiness AnalystAuthor Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 6
  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now