Append Data In Worksheet To Access Table

M

Martin

There is an Access table on the network. 15 users who do not have Access are
connected to the network. Is there a way for each user to be able to enter one
or more rows containing 3 or 4 columns to Excel on his machine and then press a
button or something and append that data to the Access table on the network?
Also would need to delete the data from the worksheet after the append. How
would I prevent more than one user from appending data at the same time? Any
suggestions on what the code would be?

Thank you very much!

Martin
 
M

Martin

Colo,

Thank you for responding!

I want each Excel user to be able to independently append data to the Access
table.

Martin
 
O

onedaywhen

Here's some example code to be run from an Excel workbook, but not the
workbook you are querying to avoid the memory leak bug (MSDN Q319998).
In absence of any detail, I've made inventions and assumptions e.g.
MyWorksheet is arranged as a database and has two columns headed
'Col1' and 'Col2' with data below etc. Post back if you require more
details.

'<code>---------------
Sub test1()

Dim oCon As Object
Dim strSql As String

Set oCon = CreateObject("ADODB.Connection")

oCon.Open = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=\\Server\Folder\MyDatabase.mdb"

strSql = "INSERT INTO MyTable (RefID, Surname)" & _
" SELECT T1.Col1 AS RefID, T2.Col2 AS Surname" & _
" FROM [MyWorksheet$] T2 " & _
"IN 'C:\MyWorkbook.xls' 'Excel 8.0;'"

oCon.Execute (strSql)

oCon.Close

End Sub
'</code>---------------
 
O

onedaywhen

Exactly how many groups did you cross- and multi-post to? Did you
re-post to all these groups to say you have already received "a great
response!!!"? Apparently not.

--
 

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

Similar Threads

Appending an existing table with imported Excel data 0
Append Data To Access Table 3
Macro Challenge – Create Table with Hours Worked and Copy Data 0
How to append data from different sheet and different workbook? 2
a way to append data from 2 different files 2
vba to copy data from sale receipt to next available column in aninventory worksheet 0
export excel data to access table using ADO 0
Make one final list from four sources 1

Top