Automating Data Import Into Access from an External Data Source

When I try to import data from an Excel file into an Access database using the below code I do get a table "test" created okay but it only has a single empty column in it with a field name F1 assigned to it.  I couldn't get all the defined fields from the Excel file with data into test table to reside in Access file.

Private Sub Command0_Click()
Dim Filepath As String
Filepath = "C:\Users\ErdenR\Desktop\Local Data Warehouse\Exports\OCEAN\201801_OCEAN_MTD.xlsb"

DoCmd.TransferSpreadsheet acImport, , "test", Filepath, True

End Sub

Can any expert help with that issue please?
trusxlsolBusiness Systems AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeCommented:
First step whenever I have to create a file import or export process is to do it manually, and save the import steps at the end of that process.

Are you sure you want to import from an xlsb file format?  I've never actually tried to import a .xlsb file format.
0
trusxlsolBusiness Systems AnalystAuthor Commented:
I do use import specs when I do data import manually to ensure that the data gets posted to the target table in the desired format.  But in this particular case this is not what I want to do.

I want to import through VBA from the source file in Excel format.  I use the xlsb format as it compacts the data hence process the transfer at a much faster rate to Access table but for my question I can work with xls or xlsx extensions as well.  

For what I need DoCmd should suffice but for some reason it creates test table without any fields or data in it.
0
PatHartmanCommented:
You left out a critical parameter.  You have to tell Access that you are using the .xlsb file type.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "test", FilePath, True
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

trusxlsolBusiness Systems AnalystAuthor Commented:
To simplify for testing purposes I did the following,

I made a copy of the file with xlsb extension and saved it as xlsx on my desktop

used the below code:

Private Sub Command0_Click()
Dim Filepath As String
Filepath = "C:\Users\ErdenR\Desktop\OCEANResultsTEST.xlsx"

DoCmd.TransferSpreadsheet acImport, , "test", Filepath, True

End Sub

Excel file has one tab in it, there was another one but deleted for testing.

In this case did not put the file type parameter (acSpreadsheetTypeExcel12) so put ", ," instead since it is xlsx.

When I ran the code from the command button it still creates "Test" table but there is only one field in it named F1.

It is not importing data from the source file into Test table.
0
Dale FyeCommented:
As Pat mentioned above, you are leaving out the parameter that defines the data source.  You need that file type parameter to tell Access what format the data is in.
0
trusxlsolBusiness Systems AnalystAuthor Commented:
Tried with the below including the file type parameter but still the same result.

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12, "Test", Filepath, True
0
Dale FyeCommented:
"I do use import specs when I do data import manually to ensure that the data gets posted to the target table in the desired format.  But in this particular case this is not what I want to do. "

OK, understand you don't want to do this in this case, but have you tried it with this dataset, to see whether the import wizard will import the file correctly?

What version of Office are you using?
0
trusxlsolBusiness Systems AnalystAuthor Commented:
Yes I can use import specs method in fact I do use it where I see fit as part of my total process. Just for the heck of it I have tried that anyways right now and yes it does work using the same data source.  

But again this is not how I want to accomplish this task.  I have routines that dump data sets generated from Access into Excel using functions/subs. They are feeding various pivot tables with dynamically updating ranges in Excel via VBA and multiple dashboards respectively and by the same token I should be able to import data into Access from Excel and DoCmd method should do the trick for this goal.

Besides the idea is that once I set this import function I can use it controlled by a macro which currently runs multiple queries so that way my entire update process would be automated which is done daily.

I am using Office 2016 (Office365 ProPlus)
0
PatHartmanCommented:
Have you considered the possibility that this spreadsheet is corrupted?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
trusxlsolBusiness Systems AnalystAuthor Commented:
I have put the data into a brand new Excel file to import from and the problem solved.  
Thank you Pat.
0
PatHartmanCommented:
You're welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Databases

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.