Data layer for excel?

[NXMold]

I havent worked with classes yet, but have read a little. I have several excel files connecting to my back end, opening a recordset, and manipulating data in the excel file. This is not the best setup for maintainability.

I am thinking I could make a data layer in the front end that can be accessed via automation from within excel. This would also allow me to use open-office, which is a current limitation.

Any tips, links, examples? Is it the same as working with the class objects from within access?

 

[kjv1611]

First, I think your question needs to be worded better somehow. It seems like going on a wild goose-chase to understand what you're asking, and leaves us open to speculation.

So, I'm guessing you mean something like this?

My best guess is that you're asking of how to do something in OpenOffice Backend?

If so, then the best forum here for that may be forum904 though it doesn't appear to be very active.

Otherwise, you might want to search for an active OpenOffice forum, perhaps specifically a development forum?

--

"If to err is human, then I must be some kind of human!" -Me

 

[NXMold]

Ignore the excel part of the equation, this is an access problem. I dont want to have SQL code in excel, I want it in the database where I have control over it.

Current excel file:

Dim cn As ADODB.Connection, rs As ADODB.Recordset, intColIndex As Integer
Set cn = New ADODB.Connection
Dim strConn As String
strConn = "DSN=MS Access Database;"
strConn = strConn & "DBQ=\\server\folder\file.mdb;"
strConn = strConn & "DefaultDir=\\server\folder;"
strConn = strConn & "DriverId=25;"
strConn = strConn & "FIL=MS Access;"
strConn = strConn & "MaxBufferSize=2048;"
strConn = strConn & "PageTimeout=5;"
strConn = strConn & "PWD=blah;"
cn.Open strConn

'Define recordset
Set rs = New ADODB.Recordset
Dim strSQL As String
strSQL = "...etc..."

What I want in excel:

      Dim oDBase As Object
      Set oDBase = CreateObject("MSAccess.Application")

      Set rs = oDBase.FancyNewClassObject
      
      Range("A1") = rs.CustomData

 

[kjv1611]

So where does Open Office come into play here?

This would also allow me to use open-office, which is a current limitation.

--

"If to err is human, then I must be some kind of human!" -Me

 

[NXMold]

Less redundancy. I don't want to keep repeating queries, etc, in varied external files. I want to create something like an API for my application, if thats the right description. I may add some ASP pages later, or connect from a yet-to-be-dertermined ERP program.

I know that you can make class objects so that, within access, you can say ABCCustomers.Add "Mikes Plumbing" instead of "SELECT * INTO tblCustomers ...".

I don't know if these class objects can be made available external to access, such as in excel.

 

[kjv1611]

I'm still not seeing the connection??? Maybe it's just me, but I doubt it at this point.

You do know that Open Office is sommething altogether different than Microsoft Office, correct? So, if you're wanting to work something between the two, it will take more than VBA programming of SQL between Excel and Access.

I honestly don't have the foggiest of how to go about trying to give you any help. And since no one else has responded yet, I doubt I'm alone.

Try going back to square one... and be VERY specific in what you want to do. I'd hate to waste my time on helping you get Excel to/from Access working correctly, and then you say, "okay, I need to do this in OpenOffice Calc," or "Open Office Backend" for instance.

--

"If to err is human, then I must be some kind of human!" -Me

 

[PHV]

NXMold,
why reinvent the wheel ?
You may play with the QueryDefs collection of the Database object, don't you ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[NXMold]

PLEASE forget about excel and open office, please. Do you understand this question:

I know that you can make class objects so that, within access, you can say ABCCustomers.Add "Mikes Plumbing" instead of "SELECT * INTO tblCustomers ...".

Can these class objects can be made available external to access (such as Excel, ASP, or perhaps OpenOffice)?