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

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

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

Hello K Dales

sorry , i didn't see your answer

regards
michel

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" <Jason@discussions.microsoft.com> wrote in message
news:C4240734-773E-492F-979C-56CEEA3878B3@microsoft.com...
> 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

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

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

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

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

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 here:
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
>
>

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

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

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

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

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

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

This was a very informative thread!!