Connect with ADODB connection - dissapearing fields in columns

[jaryszek]

Hi Guys,

I am loading database from Excel to Acces using AdodB Connection.
(you can see my workbook in attachment TeleIBStest file). Please run Mod_SQL module only for connect with ADODB access database.

Problem is with Columns F and G.

There are multiple empty fields but some of them have values.
When I am uploading table for access database there is no values in access base. So all values from table are uploaded but these 2 columns not.

Why ?!

I have tried all methods in order to solve it.

In attachment i have added also TeleIBS - there is my access *.mdb file.

In excel for test in Sheet "Listy" you have to change range "A5" and give your link (path) for access database.
In my case : "C:\Users\ljar01\Desktop\Net\TeleIBS.mdb"

Please Guys help me,
Jacek Antek

 

[Ranman256]

just attach the excel book as a table.

 

[gblack]

I am not sure why that's happening, but try this:

Instead of choosing to "Import" the file, choose to "Link": Meaning from Access: Click the External Data tab on the ribbon>>Excel
Then choose the radio button: "Link to the data source by creating a linked table"

Then once you have the Excel linked table in your "Tables" Section (within MS Access).. right click the Linked Excel Table and choose "Convert To Local Table"

You should have your data. At least it worked for me...

GL,
Gary

 

[jaryszek]

Thank You Gary,

This is working of course.

Problem is that each time when User is using SQL module there are following steps:

1. Old table (tb and login for example tb_ipon00) is dropped.
2. New table with columns headers from Excel is created.
3. Existing table from Excel is inserting into Access Table.

So problem is within Excel Table and source data - there is someting blocking these 2 columns.

Warm regards,
Jacek Antek

 

[jaryszek]

It is very, very strange.
I had found work around here.

I put sign "'" in first cell under my headers of these 2 not uploading columns and this worked !

So in empty cells i put special sign and all columns with values are inserting into my access database! wow why is that ?

Jacek

 

[gblack]

The single quote tells Excel that it's a text field... I was actually going to tell you to look for the single quote character in the cells with data. I wouldn't have thought to add it to the first record.

Hmm... I don't know... I guess MS Access needed the quote to post the field as text... I always thought that was the default... so I don't know why... but gratz!

It's a good one to know for future reference.

-G

 

[jaryszek]

Thank you gblack.

Maybe Access need to have marked column with quote to see whole as text as you said...maybe...

Jacek