ODBC Question

[Pilly170]

Hi,
I have a query within vba, it querys my SQL server. Now the query works fine, but is there a way to stop the login box appearing, by setting the connection settings/logins/passwords within vba?
I normally use php, so this is all new to me. any help or pointers would be great.


Function Make_MIS_DATA_Table()
Dim MIS1 As String

MIS1 = "SELECT dbo_TurnOverDaily.Date, dbo_TurnOverDaily.Month, dbo_TurnOverDaily.InvTOver, dbo_TurnOverDaily.InvProf12, dbo_TurnOverDaily.InvProf8, dbo_TurnOverDaily.InvRealProf, dbo_TurnOverDaily.CNTOver, dbo_TurnOverDaily.CNProf12, dbo_TurnOverDaily.CNProf8, dbo_TurnOverDaily.CNRealProf, dbo_TurnOverDaily.NoOfInvs, dbo_TurnOverDaily.NoOfItems INTO MIS_Monthly_Data FROM dbo_TurnOverDaily WHERE (((dbo_TurnOverDaily.Month)=Format(Date(),'yymm')));"

DoCmd.SetWarnings warningsoff
DoCmd.RunSQL MIS1
DoCmd.SetWarnings warningson
End Function

 

[Crowley16]

yes, it is possible to set all the settings within vba...

usually you would include all the settings within a connection string, and pass this to a adodb recordset...

I've got some code which does this for an example:

Set cn = New ADODB.Connection
cn = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID='urID';password='urPswd';Initial Catalog='Repository';Data Source='src'"
cn.Open
   
   Set rs = New ADODB.Recordset
   With rs
      Set .ActiveConnection = cn
      .Source = sqlStr
      .Open
      'do stuff
   end with

Procrastinate Now!

 

[Pilly170]

so if i had for example - my server is 192.168.200.6, login is phil password is test. Database is Misdata.

So how would my query fit with the code you've given me?
and also how do i run it???

Ive seen so many examples but cannot get my head around it.

 

[irehab2]

Hi.

I have a problem that’s been plaguing me for two weeks now. I have several years of experience developing standalone Microsoft access database solutions, but never using multiple database connections. My problem stems from trying to develop connection strings to link three databases using a password in each but without using a username. I looked up DAO and ADO connection strings in the help files and I was given practically six or so variations on how to do it using ADO and DAO formats and I keep getting an “invalid password error”.

Can anyone show me how to open up and access a password secured MS Access database that does not have user accounts but has a database password, the miners thanks to the local phone preferably using the DAO format although I’ll take any format at this time ex. ADO)?

Is the default username Admin or is it the logon username?

Many thanks!