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
2, 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)
:
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
2). 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
:
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
:
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