Excel connection to ODBC database

[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!

 

[Jacob Skaria]

http://www.datamystic.com/datapipe/excel_odbc.html

If this post helps click Yes

 

[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

 

[Bob I]

That would be database connection TO Excel, not from.

 

[Poppe]

Hi

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

Can anybody help ? For example with VB script ?

Thanks!

 

[Bob I]

Excel doesn't SEND data. Consider asking the folks in the group for your
database, as you want "pull" the Excel information, working from the
database end.

 

[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!

 

[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

 

[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

 

[rohitsingh15]

Hi poppe

iam also looking for a similar solution i.e. I want to connect to oracle database through excel. Please give me the steps so that I can also perform this activity.
Thanska lot

Rohit

 

[rohitsingh15]

Hi Ed Ferrero

Even i am also looking for a similar solution. But iam new to VB. I will appreciate if you let me know the steps to perform this activity i.e. connecting excel with oracle and transferring data from excel to oracle database.

Let me know the step by step flow.

Thanks
Rohit

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