Transferring Excel Columns to Access Fields

[mahmud1180]

Hi there!! I want to run a VBA code that makes a column in the excel file and then transfer it to Access with a certain Field Name. Then this excel Column is replaced by another set of Values and I want to transfer it to Access with another Field Name.I have a recurring procedure so, I can not use the AccessImport Method. I can add values cell by cell but I want to avoid that as it is gonna take more time. Can anyone help? Thanks a lot!!

 

[sheetspread]

Are you inserting the fields separately? That's the only difference I can think of from how I ran the code (which aligned fine):

Excel 2010
	I	J	K	L
3	North	South	East	West
4	1	2	3	4
5	2	4	6	8
6	3	6	9	12
7	4	8	12	16
8	5	10	15	20
9	6	12	18	24
10	7	14	21	28
11	8	16	24	32
12	9	18	27	36
13	10	20	30	40
14	11	22	33	44
15	12	24	36	48
16	13	26	39	52
17	14	28	42	56
18	15	30	45	60
19	16	32	48	64
20	17	34	51	68
21	18	36	54	72
Sheet1

Public Sub DoTrans()


Set cn = CreateObject("ADODB.Connection")
dbPath = Application.ActiveWorkbook.Path & "\Test2.mdb"
dbWb = Application.ActiveWorkbook.FullName
dbWs = Application.ActiveSheet.Name
scn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & dbPath
dsh = "[" & Application.ActiveSheet.Name & "$]"
cn.Open scn

ssql = "INSERT INTO Data ([North], [South], [East], [West]) "
ssql = ssql & "SELECT * FROM [Excel 8.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh


cn.Execute ssql




End Sub

(running the code again inserts another copy of the data directly below without any distortion)

 

[mahmud1180]

Are you inserting the fields separately? That's the only difference I can think of from how I ran the code (which aligned fine):

Yes! I am Inserting the Fields separately. Actually, I am creating a new Field name every time before inserting values into the Field.

Public Sub DoTrans()

Dim ORs As New ADODB.Recordset
Dim oConn As New ADODB.Connection
Set cn = CreateObject("ADODB.Connection")
dbpath = Application.ActiveWorkbook.Path & "\DataBase3.accdb"


dbwb = Application.ActiveWorkbook.FullName




dbWs = Application.ActiveSheet.Name
scn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbpath
dsh = "[" & Application.ActiveSheet.Name & "$D4:D80]"
cn.Open scn


oConn.Open "Provider=Microsoft.ACE.OLEDB.12.0;" & _
           "Data Source=Z:\Access\BackTestSample.xlsb;" & _
           "Extended Properties=""Excel 8.0;"""
oConn.Close


NameField = ThisWorkbook.Worksheets("BackTest").Cells(4, 4)


'cn.Execute "ALTER TABLE BackTest ADD COLUMN [" & NameField & "] Number;"


ssql = "INSERT INTO BackTest ([" & NameField & "]) "
ssql = ssql & "SELECT * FROM [Excel 12.0;HDR=Yes;DATABASE=" & dbwb & "]." & dsh




Debug.Print ssql


cn.Execute ssql


End Sub

 

[sheetspread]

Yes! I am Inserting the Fields separately. Actually, I am creating a new Field name every time before inserting values into the Field.

SQL INSERT adds new records to a table, it doesn't change existing records (unlike UPDATE), that's why you're getting the shifted data arrangement