Hi

New to the forums,
ive tried manipulating the code below to suit but it keep failing with the error Syntax error from Clause

Any ideas?

What i'm actually after is where a range will be selected and copied into this temp table in a database, with on the right hand side with username, timestamp and domain.
Ive found the below but i then need vba to copy that down against the rows.

Ive spent 5 hours on various sites trying to get this to work!

Sheets("Setup").Select
range("?").Value = Environ$("Username")
range("?").Value = Environ$("userdomain")
range("?").Value = Now
Rob

Sub Access_MakeTable()
'==================================================================
' Procedure - Access_MakeTable()
' Creates a Table in Access using Excel worksheet as Datasource
'==================================================================

Dim cn As Object
Dim strQuery As String
Dim myDB As String
Dim xlLoc As String
Dim N As Double
Dim errorMsg As String

On Error GoTo errHandler

Set cn = CreateObject("ADODB.Connection")

myDB = "C:\Users\robert.tuby\Documents\Scheduling Database.accdb"
xlLoc = "C:\Users\robert.tuby\Documents\TESTDB.XLSM"

With cn
.Provider = "Microsoft.ACE.OLEDB.12.0" 'For *.ACCDB Databases
.ConnectionString = myDB
.Open
End With

strQuery = "SELECT * INTO tbl_Sample FROM [SETUP] IN " & xlLoc & ";"
Debug.Print strQuery
cn.Execute "DROP TABLE tbl_Sample"

skip:
cn.Execute strQuery
cn.Close

finished:

Set cn = Nothing
Exit Sub

errHandler:
'If Access Table does not exist the DROP TABLE will fail, resume skip to continue with the MakeTbl query
If Err.Number = -2147217865 Then
Resume skip
Else
errorMsg = "An error has occurred." & Chr(10) & Chr(10) & Err.Number & " - -" & Err.Description
MsgBox errorMsg, vbCritical, "Error Message - " & ThisWorkbook.Name
Resume finished
End If

End Sub