Not a valid file name

Hi there,

I am trying to write a code to connect my excel userforms to my access database to launch queries from excel.
However, I get an error saying that "cntStudConnection.Open ConnectionString:=cnctSource" is not a valid file name and microsoft has no help solutions.

Here is my code:

Please Login or Register  to view this content.

I am quite new to this so any help is welcomed !

Thank you

I've removed the ":", and now get "data source name not found and no default driver specified"
But i've checked the location several times and do no see any issues

thank you for your help !

Is this correct?

Please Login or Register  to view this content.

Originally Posted by jindon

Is this correct?

Please Login or Register  to view this content.

Good catch! That can't possibly be correct. That will result in a string like this, which is nonsense.

C:\Users\Hala\Documents\My SpreadsheetsC:\Users\Hala\Documents\BBB\Dummydata1.accdb

Where is your Access file, and does it depend on the location of your Excel file?

My access database is in "C:\Users\Hala\Documents\BBB\Dummydata1.acdb"

I've tried removing "Thisworkbook.path" and now get "Data source name not found and no default driver specified".

Please Login or Register  to view this content.

is still highlighted

Yes sorry ! Made a typo error but its correctly written in my code

How about

Please Login or Register  to view this content.

Yes it worked thank you !!

I am calling this connection with a command button with code

Please Login or Register  to view this content.

Could I set a message box as the range instead of the sheet ? I do not want the data to appear on the sheet.

Yes it worked thank you !!

I am calling this connection with a command button with code

Please Login or Register  to view this content.

Could I set a message box as the range instead of the sheet ? I do not want the data to appear on the sheet.

Not really sure though...

Please Login or Register  to view this content.

Yes that works !

I've set the range as msg in my command button code and get this
AufL3BNrJqZs54HJqIHRIhIfI2bXHvWUDKC4ynEnMANF.jpg

But i remember being told that a msgbox has a character limit which might be why it does not display all the values that it should.

Would calling a userform that displayed the table be possible ?

Possible, something like

Please Login or Register  to view this content.

Instead of msgbox...

Yes, i've created the new userform with the text box as big as the form and edited the module function to set msg as the textbox in the new userform

I just have to now find a way to insert the data into a table. I get everything in one line

Aq8bJ9p42304Zc6D_jzmo4M5CwDHmNBbs9iClGMZsQjR.jpg

Unfortunately i cannot find anything online that indicated that i could display that so not sure if it is even possible ?

Try replace vbLf with vbCrLf in msg.

I've tried vbNewline, vbCrLf, vbCr, vbLf but still get a line result

Have you set the MultiLine property for TextBox to True?

Sorry i did not know about that property !
It works now thank you !

I just have two last questions if you do not mind,
1. I have 1000 values in my access database and the textbox only displays the information that fits into the frame. Is there any other tool that would solve this ? (Like a scroll bar maybe ?)
2. Also, since i apparently cannot create a table with this data should i assume that i cannot create a graph either ?

You can also set AutoSize property to True and see how it goes.
Also you can select ScrollBars property to show.

All done thank you !

Marking this thread as solved