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]

Does the TransferSpreadsheet method work for you?

 

[Joe4]

Cross-posted: Transferring Excel Columns to Access Fields

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule #10 here: Forum Rules).

This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.

For a more complete explanation on cross-posting, see here: Excelguru Help Site - A message to forum cross posters).

 

[mahmud1180]

@ SheetSpread Transfer spread sheet does not work for me. It will work If I can only change the Field name everytime and post the data there without deleting the data transferred previously (which I haven't been able to do)

@Joe4 Sorry for the cross posting. Did not know about the rules about it. I am on the verge of the deadline of a project. So, I was looking for a quick answer from anywhere.

 

[sheetspread]

@ Transfer spread sheet does not work for me. It will work If I can only change the Field name everytime and post the data there without deleting the data transferred previously (which I haven't been able to do)

So have the code change the field name (or temporarily change it then back).

 

[mahmud1180]

So have the code change the field name (or temporarily change it then back).

I tried changing the Field name but when I use the transferspreadsheet method the other fields get vanished. I am only left with the new Field.

 

[sheetspread]

Does this help?

sql - Using Excel VBA to export data to MS Access table - Stack Overflow

 

[mahmud1180]

Does this help?

sql - Using Excel VBA to export data to MS Access table - Stack Overflow

I think this may help. I get an error while trying that code though.

Public Sub DoTrans()



  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 & "$D5:D5000]"
  cn.Open scn


cn.Execute "ALTER TABLE Test ADD COLUMN Age3 TEXT(25)"


  ssql = "INSERT INTO Test ([Age3]) "
  ssql = ssql & "SELECT * FROM [Excel 12.0;HDR=YES;DATABASE=" & dbWb & "]." & dsh
      
  cn.Execute ssql




End Sub

This is the error: The INSERT into statement contains the following unknown field name: '10000000'. Make sure you have typed the name correctly( Could not post an image here )

 

[sheetspread]

Is 10000000 the field name? Exactly?

Check that it's named as such everywhere

Otherwise it could be one of these problems:

FWIW,

I have seen this behaviour a bit with Append queries.
I find that opening the query in Design view, and reselecting the field name on the grid, usually fixes the issue.

Denis

 

[mahmud1180]

Is 10000000 the field name? Exactly?

Check that it's named as such everywhere

Otherwise it could be one of these problems:

There is no filed name like that. There is nothing with that name.

I tried with the design mode but it still gives the same error