Excel connection to ODBC database

P

Poppe

Hi

I have excel 2007 .xlsx file. It contains exactly same rows as my database.

How can i create an automated connection, that updates the data to a
database via ODBC?

For example:

I update a row in excel file -> The updated value goes automatically into
the database.

Thanks for your help!
 
P

Poppe

Hi

Your answer creates an ODBC connection into the excel file.

I do not want this.

I have already the ODBC connection created under administrative tools to my
database that is located in a server (sybase sql).

I need to create connection from the excel file into this database via ODBC,
so that the updates to excel file go automaticallly into the database table.

BR,
Poppe
 
P

Poppe

Hi

The data needs to move from an excel chart to the database.

Can anybody help ? For example with VB script ?

Thanks!
 
P

Poppe

Hi

I'm going to write a visual basic code, that enables the sending action back
to the database with activeX (ADO). It seems to be the best choice in this
situation.

Basically it reads the cels and sends them back to the tables in the database.

If somebody else has already done this, i'd appreciate code example.

Thanks!
 
E

Ed Ferrero

Hi Poppe,

Here is a way to do it using DAO. You need to set a reference to 'Microsoft
DAO 3.6 Object Library' in the VBE Tools -> References menu before running
this code.

AFAIK this works on all versions of Excel from 95 on.
Hopefully this will get you started.

Ed Ferrero
www.edferrero.com

==============================================

Option Explicit

' change a database record using DAO

Sub CopyChangedRecords()

'Dim DataSource As Variant
Dim name_of_database As String
Dim WS1 As Workspace
Dim Db1 As Database
Dim Rs1 As Recordset
Dim findstring As String

' this sample makes a change to an Access database table
' database: SciFi.mdb
' Table: Publisher
' Fields: Publisher text - primary key
' Country text

name_of_database = "D:\Documents\Developent\Ed\EIS\SciFi.mdb"


' use DAO to write to the database
With DBEngine
Set WS1 = .CreateWorkspace(Name:="Work_S1", _
UserName:="Admin", _
Password:="")
.Workspaces.Append WS1
End With
Set Db1 = WS1.OpenDatabase(Name:=name_of_database, ReadOnly:=False)
Set Rs1 = Db1.OpenRecordset(Name:="Publisher", Type:=dbOpenDynaset)

' update records
' find record by searching primary key

findstring = "[Publisher]='Avon'"
With Rs1
.FindFirst findstring
.Edit
.Fields("Country").Value = "UK"
.Update
End With

' clean up
Db1.Close
Set Rs1 = Nothing
Set Db1 = Nothing
Set WS1 = Nothing

End Sub
 
P

Poppe

Hi

What is the difference between ADO and DAO?

I was able to write a code with ADO, the excel chart now is able to send
data to database, so my problem is pretty much solved. It uses ODBC
connection.

BR,
Poppe
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top