Export ACCESS to Lotus, with ASP

O

omarochoa

New Member
Joined
Oct 20, 2005
Messages
4
I'm currently working on a export tool, from an Access, database to Lotus, I've seen Query a Lotus 123 File message from NateO, that's where I gto the idea, I'm trying to implement this in a different way, let me send you the code:

Code: 
<HTML>
<HEADER>
</HEADER>
<BODY>
<%


Dim LotusCn 'As ADODB.Connection
Dim rsLotus 'As ADODB.Recordset
Dim strSql 'As String

Set LotusCn = CreateObject("ADODB.Connection")
Set rsLotus = CreateObject("ADODB.Recordset")



LotusCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\ot\Test.wk1;" & _
"Extended Properties=Lotus WK1;Persist Security Info=False"

strSql = "SELECT * FROM [a0..a418]"

rsLotus.Open strSql, LotusCn


for x=0 to 418
Response.Write(rsLotus.Fields.Item(0) & "
")
rsLotus.MoveNext
next

LotusCn.Close
Set rsLotus = Nothing
Set LotusCn = Nothing
%>
</BODY>
</HTML>

As you can see this is a ASP application, with this one I can read what is on the Lotus file, unfortunately, when I've tried to update the file it is impossible, I've tried this thinking that any field on the Lotus file can be modified, it sends me and error,

when I ask for rsLotus.AddNew, it tells me


ADODB.Recordset (0x800A0CB3)
Object or provider could not execute the required operation.

And if I try to assign a value to the recordset
rsLotus.MoveFirst
rsLotus.Fields.Item(0)=one

I get the same error, I will really appreciate your help on this issue, I've been working on this for days.
 

Excel Facts

Convert text numbers to real numbers
Select a column containing text numbers. Press Alt+D E F to quickly convert text to numbers. Faster than "Convert to Number"
Sort by date Sort by votes
Hi and welcome to the board :)

The Jet OLE DB Provider is optimized to be used with Microsoft Databases (i e "Access-databases) and to some extend with Excel.

It may be limited when it's used together with WK-files and therefore not able to update.

Have You tried to use a raw SQL Query (UPDATE) instead?
 
Upvote 0
Hi XL-Dennis, Thanks for your reply!

Is just that I don't know how to preform a raw SQL query to the database, can you give an example?

One question is, I've seen some examples of this code that are supposed to be working in Visual Basic, but unfortunately in ASP, that has a similar programming, the commands are not updateable.

One other question is , if it could be a problem on my initial SQL statement when I open the file, I was thinking, maybe I should not choose the range manually, i.e. [a1..418], and since in Lotus 123, one of the first versions, there's no way to name the ranges, I'm a little lost here.

Kind Regards too from Mexico City!
 
Upvote 0
Hi,

Sorry for late reply but here it comes:

The following sample add a record to the bottom of the list in the target sheet.

Code: 
Option Explicit

Const stCon As String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=c:\Bok1.xls;" & _
                        "Extended Properties='Excel 8.0;HDR=Yes';"


Sub UpDate_Source()
Dim cnt As ADODB.Connection
Dim stSQL As String
Dim vaData As Variant

vaData = ActiveSheet.Range("A2:C2").Value

'The target sheet in the target workbook is named Adam.
stSQL = "INSERT INTO [Adam$] (Dept,Amount_1,Amount_2) " & _
        "VALUES('" & vaData(1, 1) & "','" & vaData(1, 2) & "','" & _
        vaData(1, 3) & "');"
        
Set cnt = New ADODB.Connection

With cnt
    .Open stCon
    .Execute (stSQL)
    .Close
End With

Set cnt = Nothing

End Sub

Try it out with You Lotus-file and let us know the outcome of it :)
 
Upvote 0
It Worked!

Hi Dennis, I've tried your suggestions and it worked. :-D

Let me send you the code

<HTML>
<HEADER>
</HEADER>
<BODY>
<%


Dim LotusCn
Dim rsLotus
Dim strSql

Set LotusCn = CreateObject("ADODB.Connection")
Set rsLotus = CreateObject("ADODB.Recordset")



LotusCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\ot\Test.wk1;" & _
"Extended Properties=Lotus WK1;Persist Security Info=False"


strSql = "Insert Into [a1..a418] (Prueba) Values ('Hola')"
rsLotus.Open strSql, LotusCn

'Prueba is the field I'm inserting data into
strSql = "Insert Into [a1..a418] (Prueba) Values ('Hola')"

rsLotus.Open strSql, LotusCn

LotusCn.Close
Set rsLotus = Nothing
Set LotusCn = Nothing


%>
</BODY>
</HTML>

Unfortunately :-? , I have two problems with this code.

1. I have to select a named field always,

The trouble comes here, I need the first row in the WK1 file because that is a Stock Number I have to change each time we start our entire process.

The format of the file I need to deliver to the lotus procedure is 1 colum containing 418 records, and a wk1 macro will pick up all that data

2. Is there anyway I can use same syntax like in Excel where you can name the sheet you want to update?, I have not found the way, I've tried nameing the sheet the same as the Lotus filename

Instead of that I have always to declare a range [a1..a418] and that's a little bit cumbersome


Thanks in advance
Code:
 
Upvote 0
Sorry the code

Code: 
<HTML>
<HEADER>
</HEADER>
<BODY>
<%


Dim LotusCn
Dim rsLotus
Dim strSql

Set LotusCn = CreateObject("ADODB.Connection")
Set rsLotus = CreateObject("ADODB.Recordset")



LotusCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=c:\ot\Test.wk1;" & _
"Extended Properties=Lotus WK1;Persist Security Info=False"


strSql = "Insert Into [a1..a418] (Prueba) Values ('Hola')"
rsLotus.Open strSql, LotusCn

strSql = "Insert Into [a1..a418] (Prueba) Values ('Hola')"
rsLotus.Open strSql, LotusCn

LotusCn.Close
Set rsLotus = Nothing
Set LotusCn = Nothing


%>
</BODY>
</HTML>
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Solved
VBA Lotus Notes using MIME HTML and attaching a PDF in Email Body
Replies
3
Views
2K
John_w
J
J
  • Solved
VBA Web Scraping - FindElementBy - DownloadCSV Button ElementNotVisibleError Runtime error 11
2
Replies
15
Views
3K
johnd3099
J
C
  • Solved
Excel 2016 32bit VBA Pulling info from Website to Sheet
Replies
2
Views
1K
CrashOD
C
H
  • Solved
VBA to export to PDF then email....
Replies
4
Views
2K
DanteAmor
DanteAmor
N
  • Question Question
Export from Excel to Access via VBA based on cell Value
Replies
0
Views
1K
NewPadawan
N

Forum statistics

Threads
1,222,703
Messages
6,167,743
Members
452,135
Latest member
Lugen

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top