VBA - SQL Database - ActiveSheet.Name

[Galviniser]

Hi,

Straight to the point but here is the code

stADO = "Provider=SQLOLEDB;Data Source=UKD1-ENG-01;Initial Catalog=" & ActiveSheet.Name & "WWALMDB;User ID=SA;Password=*****"

Basically this works, however instead of it pulling from: (sheet name)WWALMDB i would like it to pull from WWALMDB(sheet name)

So when i tried this code:

stADO = "Provider=SQLOLEDB;Data Source=UKD1-ENG-01;Initial Catalog=" & "WWALMDB;User ID=SA;Password=*****" & ActiveSheet.Name

It did not work, i am getting an error saying:

(There has been an error during refresh: -2147217843 Login failed for user 'SA'.)

i have tried different variations of the code from putting the "ActiveSheet.Name" in different places

It also works if you type WWALMDB_Sheet1 where Sheet1 is the name of the sheet. But i would like it to automatically do it depending on the sheet name, not have to change the code every time.

I hope there is enough info here

Regards

Chris

 

[FormR]

Basically this works, however instead of it pulling from: (sheet name)WWALMDB i would like it to pull from WWALMDB(sheet name)

Hi, welcome to the board - you can try like this:

STADO = "Provider=SQLOLEDB;Data Source=UKD1-ENG-01;Initial Catalog=WWALMDB" & ActiveSheet.Name & ";User ID=SA;Password=*****"

It also works if you type WWALMDB_Sheet1 where Sheet1 is the name of the sheet.

Or, if that underscore is important:

STADO = "Provider=SQLOLEDB;Data Source=UKD1-ENG-01;Initial Catalog=WWALMDB_" & ActiveSheet.Name & ";User ID=SA;Password=*****"

 

[Galviniser]

Thank-you for your reply,

It has worked, which proves it was just syntax, the way i was writing the code

Really appreciate it

Regards

Chris

 

[Galviniser]

Just to clarify, what is the ruling behind this syntax? Why is it written this way?

Regards

Chris

 

[FormR]

Just to clarify, what is the ruling behind this syntax?

Hi, there's no rule as such, we are simply inserting the active sheet name into the string at the right place. It looks like you were trying to append it at the end of the string instead of after the "WWALMDB_" part.

Try googling "concatenating strings with variables in VBA" for some tutorials / explanations.