Data From Excel Range To Existing Access Table
 

Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code that
will allow me to push a button from Excel and have the range of data uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is called
"Table1". The fields in the Access table have already been defined and match
up with the number of data points in the range. Could someone help with some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

First, add a reference to Microsoft ActiveX Data Objects to your project

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Database.mdb" ' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("C1").Value & "', '" & Range("C2").Value & "', '" _
& Range("C3").Value & "', '" & Range("C4").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
--
- K Dales


"Jason" wrote:

Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code that
will allow me to push a button from Excel and have the range of data uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is called
"Table1". The fields in the Access table have already been defined and match
up with the number of data points in the range. Could someone help with some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

Hello

I hope this help you

Sub exportDatas_Excel_Access()
'Activate Microsoft ActiveX Data Objects x.x Library
Dim Conn As New ADODB.Connection
Dim rsT As New ADODB.Recordset
Dim Cell As Range
Dim i As Integer

With Conn
.Provider = "Microsoft.JET.OLEDB.4.0"
.Open "C:\dataBase.mdb"
End With

With rsT
.ActiveConnection = Conn
.Open "Table1", LockType:=adLockOptimistic
End With

'export range("C1:C4") in Access table
With rsT
.AddNew
For i = 0 To 3
.Fields(i).Value = Cells(i + 1, 3)
Next i
.Update
End With

rsT.Close
Conn.Close
End Sub


Regards ,
miche

--
michelxl
-----------------------------------------------------------------------
michelxld's Profile: http://www.excelforum.com/member.php...fo&userid=1736
View this thread: http://www.excelforum.com/showthread.php?threadid=39239

Data From Excel Range To Existing Access Table
 

Hello K Dales

sorry , i didn't see your answer

regards
michel


--
michelxld
------------------------------------------------------------------------
michelxld's Profile: http://www.excelforum.com/member.php...o&userid=17367
View this thread: http://www.excelforum.com/showthread...hreadid=392393

Data From Excel Range To Existing Access Table
 

This might be a help for getting data to and from Excel and Access: It
includes examples of using variables in SQL queries.
http://www.bygsoftware.com/examples/sql.html

Or you can get there from the "Excel with Access Databases" section on page:
http://www.bygsoftware.com/examples/examples.htm

It demonstrates how to use SQL in Excel's VBA to:

* create a database,
* create a table
* insert records
* select records,
* update records,
* delete records,
* delete a table,
* delete a database.

DAO and ADO files available.

You can also download the demonstration file called "excelsql.zip".

The code is open and commented.


--
Andy Wiggins FCCA
www.BygSoftware.com
Excel, Access and VBA Consultancy
-

"Jason" wrote in message
...
Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code
that
will allow me to push a button from Excel and have the range of data
uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is
called
"Table1". The fields in the Access table have already been defined and
match
up with the number of data points in the range. Could someone help with
some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

Thanks to all for the responses. I haven't had time to try out the code, but
it looks like I've gotten specific feedback and I believe I'll be able to
make it work. Thanks again.

Jason

"Jason" wrote:

Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code that
will allow me to push a button from Excel and have the range of data uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is called
"Table1". The fields in the Access table have already been defined and match
up with the number of data points in the range. Could someone help with some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

Hi,

While I can't say I understand all the code (I'm a novice), it works great.
One quick follow up question. Suppose I were to change the range to be
transferred in excel so that it was going across in a row instead of going
down a column (i.e. range A2:D2). How could I adjust the code below to make
this work. I've tried various attempts but have been unsuccessful. Thanks.

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("C1").Value & "', '" & Range("C2").Value & "', '" _
& Range("C3").Value & "', '" & Range("C4").Value & "')"

Jason




"K Dales" wrote:

First, add a reference to Microsoft ActiveX Data Objects to your project

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Database.mdb" ' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings


' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
--
- K Dales


"Jason" wrote:

Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code that
will allow me to push a button from Excel and have the range of data uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is called
"Table1". The fields in the Access table have already been defined and match
up with the number of data points in the range. Could someone help with some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

What I am doing there is building a string to use as an SQL command to add a
line to the database. Let's say (for illustration) the values you want to
add to the database are "1", "5", "22", "HELLO". The SQL command to do that
would read:
INSERT INTO [Table1] VALUES('1','5','22','HELLO')
The order I give the values in corresponds to their resulting position in
the database fields.

In the code I am just splitting up the string and inserting the values from
the specific cells. Range("C1").Value refers to the value that is in cell
C1, etc.:
SQLStr = "INSERT INTO [Table1] Values('" & Range(C1).Value & "','" ...
Note how I also have to put the single quotes in there. This treats
everything as a text value but that was the safe option (Access can usually
do any necessary conversion when it adds the values to the table; it is
really best though to send numbers as numbers, dates as dates, etc.)

So - to get to the point: to use different values, e.g. A2:D2, just
substitute the proper cell addresses into the statement: replace
Range("C1").Value with Range("A2").Value, Range("C2").Value with
Range("B2").Value, etc.
--
- K Dales (long-winded answer but I hope informative)


"Jason" wrote:

Hi,

While I can't say I understand all the code (I'm a novice), it works great.
One quick follow up question. Suppose I were to change the range to be
transferred in excel so that it was going across in a row instead of going
down a column (i.e. range A2:D2). How could I adjust the code below to make
this work. I've tried various attempts but have been unsuccessful. Thanks.

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("C1").Value & "', '" & Range("C2").Value & "', '" _
& Range("C3").Value & "', '" & Range("C4").Value & "')"

Jason




"K Dales" wrote:

First, add a reference to Microsoft ActiveX Data Objects to your project

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Database.mdb" ' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings


' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
--
- K Dales


"Jason" wrote:

Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code that
will allow me to push a button from Excel and have the range of data uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is called
"Table1". The fields in the Access table have already been defined and match
up with the number of data points in the range. Could someone help with some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

Excellent! That helps a lot, I'm following you know. Just as a follow up,
is it possible then use the same logic, but instead of referencing individual
cells, reference a named range? For example, could I take the named range
"rng_Data", which referes to the range "A1:Z1", and substitute that in and
have it still work? Thanks again.

Jason

"K Dales" wrote:

What I am doing there is building a string to use as an SQL command to add a
line to the database. Let's say (for illustration) the values you want to
add to the database are "1", "5", "22", "HELLO". The SQL command to do that
would read:
INSERT INTO [Table1] VALUES('1','5','22','HELLO')
The order I give the values in corresponds to their resulting position in
the database fields.

In the code I am just splitting up the string and inserting the values from
the specific cells. Range("C1").Value refers to the value that is in cell
C1, etc.:
SQLStr = "INSERT INTO [Table1] Values('" & Range(C1).Value & "','" ...
Note how I also have to put the single quotes in there. This treats
everything as a text value but that was the safe option (Access can usually
do any necessary conversion when it adds the values to the table; it is
really best though to send numbers as numbers, dates as dates, etc.)

So - to get to the point: to use different values, e.g. A2:D2, just
substitute the proper cell addresses into the statement: replace
Range("C1").Value with Range("A2").Value, Range("C2").Value with
Range("B2").Value, etc.
--
- K Dales (long-winded answer but I hope informative)


"Jason" wrote:

Hi,

While I can't say I understand all the code (I'm a novice), it works great.
One quick follow up question. Suppose I were to change the range to be
transferred in excel so that it was going across in a row instead of going
down a column (i.e. range A2:D2). How could I adjust the code below to make
this work. I've tried various attempts but have been unsuccessful. Thanks.

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("C1").Value & "', '" & Range("C2").Value & "', '" _
& Range("C3").Value & "', '" & Range("C4").Value & "')"

Jason




"K Dales" wrote:

First, add a reference to Microsoft ActiveX Data Objects to your project

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Database.mdb" ' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings


' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
--
- K Dales


"Jason" wrote:

Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code that
will allow me to push a button from Excel and have the range of data uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is called
"Table1". The fields in the Access table have already been defined and match
up with the number of data points in the range. Could someone help with some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

Hi

your code works great and I have modified a little bit so make it once
insert it to Access, it will then be deleted and looks like it is "moved" to
Access. However,

I have 2 problems he
1. the first field i have in the DB is an autonumber field, how can I handle
it ? if I put an empty value, it will prompt error

2. the process seems a little slow, it seems using loop from row to row
quite slow.

thanks a lot

Leung


"michelxld" wrote:


Hello

I hope this help you

Sub exportDatas_Excel_Access()
'Activate Microsoft ActiveX Data Objects x.x Library
Dim Conn As New ADODB.Connection
Dim rsT As New ADODB.Recordset
Dim Cell As Range
Dim i As Integer

With Conn
.Provider = "Microsoft.JET.OLEDB.4.0"
.Open "C:\dataBase.mdb"
End With

With rsT
.ActiveConnection = Conn
.Open "Table1", LockType:=adLockOptimistic
End With

'export range("C1:C4") in Access table
With rsT
.AddNew
For i = 0 To 3
.Fields(i).Value = Cells(i + 1, 3)
Next i
.Update
End With

rsT.Close
Conn.Close
End Sub


Regards ,
michel


--
michelxld
------------------------------------------------------------------------
michelxld's Profile: http://www.excelforum.com/member.php...o&userid=17367
View this thread: http://www.excelforum.com/showthread...hreadid=392393

Data From Excel Range To Existing Access Table
 

No, it is not so easy since you are building a string and you need to put the
actual values in - as if you are typing them. SQL (Structured Query
Language) is a way of requesting data from a database that is independent of
both the calling application and the database. That allows it to be very
flexible and useful for sharing data across applications, but the problem is
that it does not itself recognize any of the application's own features.
What I mean in terms of your question is that SQL has no idea what an Excel
range is, so you can't just say use Range("A1:Z1"). You need to actually
specify the values.

If this is something you will be using repeatedly, though, for a lot of
different database tables and Excel ranges, the best thing would be to build
your own function to convert a range (any arbitrary range) to the proper SQL
"Values" list. E.g:

Function BuildSQL(TableName as String, ValueRange as Range) as String
Dim DataCell as Range, SQL as String, FirstCell as Boolean
SQL = "INSERT INTO " & TableName & " Values("
FirstCell = True
For Each DataCell in ValueRange
If Not(FirstCell) Then SQL = SQL & ","
SQL = SQL & "'" & DataCell.Text & "'"
FirstCell = False
Next DataCell
SQL = SQL & ")"
BuildSQL = SQL
End Function

I hope you still follow. I am just using the specified range to build the
proper SQL statement, so the actual process of sending the command through
VBA becomes simple and could be used over and over for many different
tables/value ranges, in fact the whole thing as a piece of reusable code
would be just this:

Sub UploadData(TableName as String, ValueRange as Range)

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Database.mdb"
MyCn.Execute BuildSQL(TableName, ValueRange)

MyCn.Close
Set MyCn = Nothing

End Sub

I haven't tested the code so hopefully I got it all right, but by now I
think you understand enough to take the idea forward. It could, in fact,
even be modified to use any database file... I will leave that to you!

--
- K Dales


"Jason" wrote:

Excellent! That helps a lot, I'm following you know. Just as a follow up,
is it possible then use the same logic, but instead of referencing individual
cells, reference a named range? For example, could I take the named range
"rng_Data", which referes to the range "A1:Z1", and substitute that in and
have it still work? Thanks again.

Jason

"K Dales" wrote:

What I am doing there is building a string to use as an SQL command to add a
line to the database. Let's say (for illustration) the values you want to
add to the database are "1", "5", "22", "HELLO". The SQL command to do that
would read:
INSERT INTO [Table1] VALUES('1','5','22','HELLO')
The order I give the values in corresponds to their resulting position in
the database fields.

In the code I am just splitting up the string and inserting the values from
the specific cells. Range("C1").Value refers to the value that is in cell
C1, etc.:
SQLStr = "INSERT INTO [Table1] Values('" & Range(C1).Value & "','" ...
Note how I also have to put the single quotes in there. This treats
everything as a text value but that was the safe option (Access can usually
do any necessary conversion when it adds the values to the table; it is
really best though to send numbers as numbers, dates as dates, etc.)

So - to get to the point: to use different values, e.g. A2:D2, just
substitute the proper cell addresses into the statement: replace
Range("C1").Value with Range("A2").Value, Range("C2").Value with
Range("B2").Value, etc.
--
- K Dales (long-winded answer but I hope informative)


"Jason" wrote:

Hi,

While I can't say I understand all the code (I'm a novice), it works great.
One quick follow up question. Suppose I were to change the range to be
transferred in excel so that it was going across in a row instead of going
down a column (i.e. range A2:D2). How could I adjust the code below to make
this work. I've tried various attempts but have been unsuccessful. Thanks.

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("C1").Value & "', '" & Range("C2").Value & "', '" _
& Range("C3").Value & "', '" & Range("C4").Value & "')"

Jason




"K Dales" wrote:

First, add a reference to Microsoft ActiveX Data Objects to your project

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Database.mdb" ' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings


' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
--
- K Dales


"Jason" wrote:

Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code that
will allow me to push a button from Excel and have the range of data uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is called
"Table1". The fields in the Access table have already been defined and match
up with the number of data points in the range. Could someone help with some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

I have a question, i see that this person wanted to do teh same thing that i
am trying to do, trying to go by column. Is there a command line i can attach
to loop until all records have been uploaded. thanks

ex. I am trying to upload ranges a12:n12
and get all the rows in those columns

"K Dales" wrote:

What I am doing there is building a string to use as an SQL command to add a
line to the database. Let's say (for illustration) the values you want to
add to the database are "1", "5", "22", "HELLO". The SQL command to do that
would read:
INSERT INTO [Table1] VALUES('1','5','22','HELLO')
The order I give the values in corresponds to their resulting position in
the database fields.

In the code I am just splitting up the string and inserting the values from
the specific cells. Range("C1").Value refers to the value that is in cell
C1, etc.:
SQLStr = "INSERT INTO [Table1] Values('" & Range(C1).Value & "','" ...
Note how I also have to put the single quotes in there. This treats
everything as a text value but that was the safe option (Access can usually
do any necessary conversion when it adds the values to the table; it is
really best though to send numbers as numbers, dates as dates, etc.)

So - to get to the point: to use different values, e.g. A2:D2, just
substitute the proper cell addresses into the statement: replace
Range("C1").Value with Range("A2").Value, Range("C2").Value with
Range("B2").Value, etc.
--
- K Dales (long-winded answer but I hope informative)


"Jason" wrote:

Hi,

While I can't say I understand all the code (I'm a novice), it works great.
One quick follow up question. Suppose I were to change the range to be
transferred in excel so that it was going across in a row instead of going
down a column (i.e. range A2:D2). How could I adjust the code below to make
this work. I've tried various attempts but have been unsuccessful. Thanks.

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("C1").Value & "', '" & Range("C2").Value & "', '" _
& Range("C3").Value & "', '" & Range("C4").Value & "')"

Jason




"K Dales" wrote:

First, add a reference to Microsoft ActiveX Data Objects to your project

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Database.mdb" ' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings


' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
--
- K Dales


"Jason" wrote:

Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code that
will allow me to push a button from Excel and have the range of data uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is called
"Table1". The fields in the Access table have already been defined and match
up with the number of data points in the range. Could someone help with some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

Hello,
I tried to use your code in a command button in excel but I am getting a
"user Defined" error message. It goes to this line in the code:

Conn As New ADODB.Connection

Can you tell me why I'm getting this error?

Thanks

"michelxld" wrote:


Hello

I hope this help you

Sub exportDatas_Excel_Access()
'Activate Microsoft ActiveX Data Objects x.x Library
Dim Conn As New ADODB.Connection
Dim rsT As New ADODB.Recordset
Dim Cell As Range
Dim i As Integer

With Conn
.Provider = "Microsoft.JET.OLEDB.4.0"
.Open "C:\dataBase.mdb"
End With

With rsT
.ActiveConnection = Conn
.Open "Table1", LockType:=adLockOptimistic
End With

'export range("C1:C4") in Access table
With rsT
.AddNew
For i = 0 To 3
.Fields(i).Value = Cells(i + 1, 3)
Next i
.Update
End With

rsT.Close
Conn.Close
End Sub


Regards ,
michel


--
michelxld
------------------------------------------------------------------------
michelxld's Profile: http://www.excelforum.com/member.php...o&userid=17367
View this thread: http://www.excelforum.com/showthread...hreadid=392393

Data From Excel Range To Existing Access Table
 

How would I append the database username and password to this code?

"K Dales" wrote:

First, add a reference to Microsoft ActiveX Data Objects to your project

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Database.mdb" ' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("C1").Value & "', '" & Range("C2").Value & "', '" _
& Range("C3").Value & "', '" & Range("C4").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
--
- K Dales


"Jason" wrote:

Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code that
will allow me to push a button from Excel and have the range of data uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is called
"Table1". The fields in the Access table have already been defined and match
up with the number of data points in the range. Could someone help with some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

How would I add code to this that will delete any data in the existing table?
I want to be able to delete any data before uploading.

"K Dales" wrote:

First, add a reference to Microsoft ActiveX Data Objects to your project

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Database.mdb" ' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("C1").Value & "', '" & Range("C2").Value & "', '" _
& Range("C3").Value & "', '" & Range("C4").Value & "')"
' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
--
- K Dales


"Jason" wrote:

Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code that
will allow me to push a button from Excel and have the range of data uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is called
"Table1". The fields in the Access table have already been defined and match
up with the number of data points in the range. Could someone help with some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

Hello,

Can you explain how this code works?

MyCn.Execute BuildSQL(TableName, ValueRange)

I can't seem to get this to work properly. Can you help me out with this one?

"K Dales" wrote:

No, it is not so easy since you are building a string and you need to put the
actual values in - as if you are typing them. SQL (Structured Query
Language) is a way of requesting data from a database that is independent of
both the calling application and the database. That allows it to be very
flexible and useful for sharing data across applications, but the problem is
that it does not itself recognize any of the application's own features.
What I mean in terms of your question is that SQL has no idea what an Excel
range is, so you can't just say use Range("A1:Z1"). You need to actually
specify the values.

If this is something you will be using repeatedly, though, for a lot of
different database tables and Excel ranges, the best thing would be to build
your own function to convert a range (any arbitrary range) to the proper SQL
"Values" list. E.g:

Function BuildSQL(TableName as String, ValueRange as Range) as String
Dim DataCell as Range, SQL as String, FirstCell as Boolean
SQL = "INSERT INTO " & TableName & " Values("
FirstCell = True
For Each DataCell in ValueRange
If Not(FirstCell) Then SQL = SQL & ","
SQL = SQL & "'" & DataCell.Text & "'"
FirstCell = False
Next DataCell
SQL = SQL & ")"
BuildSQL = SQL
End Function

I hope you still follow. I am just using the specified range to build the
proper SQL statement, so the actual process of sending the command through
VBA becomes simple and could be used over and over for many different
tables/value ranges, in fact the whole thing as a piece of reusable code
would be just this:

Sub UploadData(TableName as String, ValueRange as Range)

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Database.mdb"
MyCn.Execute BuildSQL(TableName, ValueRange)

MyCn.Close
Set MyCn = Nothing

End Sub

I haven't tested the code so hopefully I got it all right, but by now I
think you understand enough to take the idea forward. It could, in fact,
even be modified to use any database file... I will leave that to you!

--
- K Dales


"Jason" wrote:

Excellent! That helps a lot, I'm following you know. Just as a follow up,
is it possible then use the same logic, but instead of referencing individual
cells, reference a named range? For example, could I take the named range
"rng_Data", which referes to the range "A1:Z1", and substitute that in and
have it still work? Thanks again.

Jason

"K Dales" wrote:

What I am doing there is building a string to use as an SQL command to add a
line to the database. Let's say (for illustration) the values you want to
add to the database are "1", "5", "22", "HELLO". The SQL command to do that
would read:
INSERT INTO [Table1] VALUES('1','5','22','HELLO')
The order I give the values in corresponds to their resulting position in
the database fields.

In the code I am just splitting up the string and inserting the values from
the specific cells. Range("C1").Value refers to the value that is in cell
C1, etc.:
SQLStr = "INSERT INTO [Table1] Values('" & Range(C1).Value & "','" ...
Note how I also have to put the single quotes in there. This treats
everything as a text value but that was the safe option (Access can usually
do any necessary conversion when it adds the values to the table; it is
really best though to send numbers as numbers, dates as dates, etc.)

So - to get to the point: to use different values, e.g. A2:D2, just
substitute the proper cell addresses into the statement: replace
Range("C1").Value with Range("A2").Value, Range("C2").Value with
Range("B2").Value, etc.
--
- K Dales (long-winded answer but I hope informative)


"Jason" wrote:

Hi,

While I can't say I understand all the code (I'm a novice), it works great.
One quick follow up question. Suppose I were to change the range to be
transferred in excel so that it was going across in a row instead of going
down a column (i.e. range A2:D2). How could I adjust the code below to make
this work. I've tried various attempts but have been unsuccessful. Thanks.

SQLStr = "INSERT INTO [Table1] " _
& "Values ('" & Range("C1").Value & "', '" & Range("C2").Value & "', '" _
& Range("C3").Value & "', '" & Range("C4").Value & "')"

Jason




"K Dales" wrote:

First, add a reference to Microsoft ActiveX Data Objects to your project

Sub UploadData()

Dim MyCn As ADODB.Connection
Dim SQLStr As String

Set MyCn = New ADODB.Connection

MyCn.Open "DRIVER={Microsoft Access Driver (*.mdb)}; " & _
"DBQ=C:\Database.mdb" ' Replace actual Access file path here
' Note: If database has userID and password, need to specify them here also
' by appending "; UID=MyUserID; PWD=MyPassword"
' - if trouble accessing the file do a net search for help on Connection
Strings


' NOTE: The above assumes all fields are TEXT data type, that is why the "'"s;
' might have trouble with other data types unless you match the format
expected
' by the database

MyCn.Execute SQLStr

MyCn.Close
Set MyCn = Nothing

End Sub
--
- K Dales


"Jason" wrote:

Greetings,

I have an excel workbook containing a range which has a single row of data
("rng_Data" in range C1:C4). I would like to be able to have some code that
will allow me to push a button from Excel and have the range of data uploaded
and appended to the bottom of an existing table in an existing Access
database. Assume database is called "Database.mdb" and the table is called
"Table1". The fields in the Access table have already been defined and match
up with the number of data points in the range. Could someone help with some
code that would transfer this data. Thanks.

Jason

Data From Excel Range To Existing Access Table
 

This was a very informative thread!!


--
Flamikey
------------------------------------------------------------------------
Flamikey's Profile: http://www.excelforum.com/member.php...fo&userid=4612
View this thread: http://www.excelforum.com/showthread...hreadid=392393