Log in to SQL using VBA in Access.

Hello, hope you guys can help me out on this one. I have an MS Access App. that uses SQL as the backend through ODBC. I would like to force my Access App. to use a specific user account every time it queries the SQL Database.

Can someone give me a code example of a VBA string I can use to completely bypass whatever user is logged into the workstation, and have my app use a user account I specify within the VBA code.

Your help is much appreciated.
JovixTry this:
"Driver={SQLServer};Server=Your_Server_Name;Databas e=Your_Database_Name;Uid=Your_Username;Pwd=Your_Pa ssword;"

And check this website (might be helpful). http://www.sqlstrings.com/|||I'd be inclined to look at sp_setapprole (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_sa-sz_6tt1.asp) instead. This allows every user to retain their own identity (so you can later blame the innocent), while allowing your application to use a single signon to the database that isn't available to the users (to allow you to protect the guilty).

-PatP|||^^ Thanks guys...to get my app running asap, I'll use the Driver string. But I'll definetely look into sp_setapprole.

Log files must be named *_log.ldf?

Hello,

I've been having problems with SQL Express when my database files are named basename.mdf and basename.ldf (e.g. on using the Access upsizing wizard).

At points that I haven't fully identified yet (I'm using VB Express clients), a second log file named basename_log.mdf gets created and SQL Express gets confused and compalins the files are corrupted.

Can anyone explain what is happening?

TIA,

Bill

SQL Server files do not really need to have a specific extension, so it might be possible that this file, although having a ldf extension is not a ldf file. Which files did the wizard create for you ?

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de|||

Hello Jens.

My problem is not with the ldf extension but with a second transaction log file getting created.

As an example:

1) Create a new Access database namedmyDatabase.mdb (containing a single table.)

2) Use the Upsizing wizard to create a new SQL database from the Access database accepting the defaults as much as possible. This creates files named myDatabaseSQL.mdf and myDatabaseSQL.ldf in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data

3) The database myDatabaseSql is already visible when you next open up MSS MSE, from where you can see the data in the one table.

4) So far everything is ok.

5) Now exit MSS MSE and start VB 2k5 Express. Without opening up any project or solution, simply open the Database Explorer and create a new connection to myDatabaseSQL. Hit the Test Connection button and once told it’s ok, immediately exit VB.

6) At this point there are now three files in C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data :

myDatabaseSQL.mdf

myDatabaseSQL.ldf

myDataBaseSQL_log.ldf (this one seems to be created by VB's Database Explorer)

7) Now open up MSS MSE again and try to get to table within myDatabaseSQL from the object explorer. On clicking on myDatabaseSQL you get the following error message

One or more files do not match the primary file of the database….. If this is an existing database the file may be corrupted and should be restored from a backup. (Microsoft SQL Server, Error 5173)

This behaviour is repeated on all my databases including Adventure Works and NorthWind.

I got the problem on the ‘smaller ‘ SQL Server Express + MSS MSE down loads on one machine and also get the problem with the ‘larger’ SSE with Advanced Services + Toolkit downloads on a second machine.

Both machines are running XP Pro + Office 2003 + 2005 Express versions of SS + VB.

Hope this lot help.

TIA

Bill

sql

Log Files - possible to read them ?

We have a user who we think is connecting to our DB through Access and
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
--
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>> We have a user who we think is connecting to our DB through Access and
>> accidently changes records. (He needs to be able to make changes
>> elsewhere so he does need write access).
>> We have tossed around the idea of giving him a read only account that he
>> would connect with but that was rejected - he will use his account with
>> write privileges.
>> So my question is - can we use the transaction logs to see what the user
>> is doing? What format is the transaction log in? Anything useful.
>> TIA - Jeff.
>>
>|||Hi,
I think that this is the completely wrong approach. If you need to see
what the client is doing you need to be using SQL Profiler. Best of all
it comes with SQL Server so you do not have to fork out any more money :-)
Jonathan
Mufasa wrote:
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>

Log Files - possible to read them ?

We have a user who we think is connecting to our DB through Access and
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspf...
log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user i
s
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
--
Graham (Pete) Berry
PeteBerry@.Caltech.edu|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspf...-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>|||Hi,
I think that this is the completely wrong approach. If you need to see
what the client is doing you need to be using SQL Profiler. Best of all
it comes with SQL Server so you do not have to fork out any more money :-)
Jonathan
Mufasa wrote:
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user i
s
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>

Log Files - possible to read them ?

We have a user who we think is connecting to our DB through Access and
accidently changes records. (He needs to be able to make changes elsewhere
so he does need write access).
We have tossed around the idea of giving him a read only account that he
would connect with but that was rejected - he will use his account with
write privileges.
So my question is - can we use the transaction logs to see what the user is
doing? What format is the transaction log in? Anything useful.
TIA - Jeff.
There are some 3rd party tools that can allow you to do this. If this is for
2000 then the one from Red-Gate is free.
http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
Andrew J. Kelly SQL MVP
"Mufasa" <jb@.nowhere.com> wrote in message
news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user
> is doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
|||In article <O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl>, jb@.nowhere.com
says...
> We have a user who we think is connecting to our DB through Access and
> accidently changes records. (He needs to be able to make changes elsewhere
> so he does need write access).
> We have tossed around the idea of giving him a read only account that he
> would connect with but that was rejected - he will use his account with
> write privileges.
> So my question is - can we use the transaction logs to see what the user is
> doing? What format is the transaction log in? Anything useful.
> TIA - Jeff.
>
>
LogReader from Lumigent software -- very useful but I'm not sure of the
cost these days.
Graham (Pete) Berry
PeteBerry@.Caltech.edu
|||I highly recommend ApexSQL Log. It is command-line driveable and has some
neat auditing capabilities. It is sql 2005 compatible and it also has an
API coming out soon.
TheSQLGuru
President
Indicium Resources, Inc.
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:%23SzOd%23$vHHA.5008@.TK2MSFTNGP05.phx.gbl...
> There are some 3rd party tools that can allow you to do this. If this is
> for 2000 then the one from Red-Gate is free.
> http://sqlserver2000.databases.aspfaq.com/how-do-i-recover-data-from-sql-server-s-log-files.html
>
> --
> Andrew J. Kelly SQL MVP
> "Mufasa" <jb@.nowhere.com> wrote in message
> news:O6JZU5$vHHA.4228@.TK2MSFTNGP06.phx.gbl...
>

log files

We have a developer that is requesting access to log file to help with
develpment process,
What if any would be a best log files for them to see ? If I look under
managament and log files there, those don't really look like they would
help..
ThanksI think you have to ask them. "What type of information is it that you wish to see though this "log
file"?".
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stup88" <Stup88@.discussions.microsoft.com> wrote in message
news:4A9222CC-9897-47D2-BB49-ECA888E21020@.microsoft.com...
> We have a developer that is requesting access to log file to help with
> develpment process,
> What if any would be a best log files for them to see ? If I look under
> managament and log files there, those don't really look like they would
> help..
> Thanks|||What type of info can you see through this log.. I sent them a copy of what
is currently in there and like I thought it wasn't much help.
"Tibor Karaszi" wrote:
> I think you have to ask them. "What type of information is it that you wish to see though this "log
> file"?".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stup88" <Stup88@.discussions.microsoft.com> wrote in message
> news:4A9222CC-9897-47D2-BB49-ECA888E21020@.microsoft.com...
> > We have a developer that is requesting access to log file to help with
> > develpment process,
> >
> > What if any would be a best log files for them to see ? If I look under
> > managament and log files there, those don't really look like they would
> > help..
> >
> > Thanks
>|||Which log? You mean the errorlog file? Basically, some startup messages and mostly more severe error
messages produced by the engine. I doubt this is very helpful to a developer...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stup88" <Stup88@.discussions.microsoft.com> wrote in message
news:12699E90-3007-4038-B9CA-EF95D1DE5737@.microsoft.com...
> What type of info can you see through this log.. I sent them a copy of what
> is currently in there and like I thought it wasn't much help.
> "Tibor Karaszi" wrote:
>> I think you have to ask them. "What type of information is it that you wish to see though this
>> "log
>> file"?".
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://sqlblog.com/blogs/tibor_karaszi
>>
>> "Stup88" <Stup88@.discussions.microsoft.com> wrote in message
>> news:4A9222CC-9897-47D2-BB49-ECA888E21020@.microsoft.com...
>> > We have a developer that is requesting access to log file to help with
>> > develpment process,
>> >
>> > What if any would be a best log files for them to see ? If I look under
>> > managament and log files there, those don't really look like they would
>> > help..
>> >
>> > Thanks
>>sql

log files

We have a developer that is requesting access to log file to help with
develpment process,
What if any would be a best log files for them to see ? If I look under
managament and log files there, those don't really look like they would
help..
ThanksI think you have to ask them. "What type of information is it that you wish
to see though this "log
file"?".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stup88" <Stup88@.discussions.microsoft.com> wrote in message
news:4A9222CC-9897-47D2-BB49-ECA888E21020@.microsoft.com...
> We have a developer that is requesting access to log file to help with
> develpment process,
> What if any would be a best log files for them to see ? If I look under
> managament and log files there, those don't really look like they would
> help..
> Thanks|||What type of info can you see through this log.. I sent them a copy of what
is currently in there and like I thought it wasn't much help.
"Tibor Karaszi" wrote:

> I think you have to ask them. "What type of information is it that you wis
h to see though this "log
> file"?".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stup88" <Stup88@.discussions.microsoft.com> wrote in message
> news:4A9222CC-9897-47D2-BB49-ECA888E21020@.microsoft.com...
>|||Which log? You mean the errorlog file? Basically, some startup messages and
mostly more severe error
messages produced by the engine. I doubt this is very helpful to a developer
..
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stup88" <Stup88@.discussions.microsoft.com> wrote in message
news:12699E90-3007-4038-B9CA-EF95D1DE5737@.microsoft.com...[vbcol=seagreen]
> What type of info can you see through this log.. I sent them a copy of wha
t
> is currently in there and like I thought it wasn't much help.
> "Tibor Karaszi" wrote:
>

log files

We have a developer that is requesting access to log file to help with
develpment process,
What if any would be a best log files for them to see ? If I look under
managament and log files there, those don't really look like they would
help..
Thanks
I think you have to ask them. "What type of information is it that you wish to see though this "log
file"?".
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stup88" <Stup88@.discussions.microsoft.com> wrote in message
news:4A9222CC-9897-47D2-BB49-ECA888E21020@.microsoft.com...
> We have a developer that is requesting access to log file to help with
> develpment process,
> What if any would be a best log files for them to see ? If I look under
> managament and log files there, those don't really look like they would
> help..
> Thanks
|||What type of info can you see through this log.. I sent them a copy of what
is currently in there and like I thought it wasn't much help.
"Tibor Karaszi" wrote:

> I think you have to ask them. "What type of information is it that you wish to see though this "log
> file"?".
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://sqlblog.com/blogs/tibor_karaszi
>
> "Stup88" <Stup88@.discussions.microsoft.com> wrote in message
> news:4A9222CC-9897-47D2-BB49-ECA888E21020@.microsoft.com...
>
|||Which log? You mean the errorlog file? Basically, some startup messages and mostly more severe error
messages produced by the engine. I doubt this is very helpful to a developer...
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Stup88" <Stup88@.discussions.microsoft.com> wrote in message
news:12699E90-3007-4038-B9CA-EF95D1DE5737@.microsoft.com...[vbcol=seagreen]
> What type of info can you see through this log.. I sent them a copy of what
> is currently in there and like I thought it wasn't much help.
> "Tibor Karaszi" wrote:

Log file

I have SQL server and many people with the same account access to this
server from our network.
They access through the registration in their client enterprise manager.
Is there any option to get the IP addresses and times for connections
created to my server in last 3 months for example?
Is there any log file or any option to set that for the future access?
regards,Show about a third party tools
try ang Lumigent Log Explorer for SQL Server
http://www.lumigent.com/products/le_sql_faq.html
or the apex tools
http://www.apexsql.com/sql_tools_log.asp
"simon" wrote:

> I have SQL server and many people with the same account access to this
> server from our network.
> They access through the registration in their client enterprise manager.
> Is there any option to get the IP addresses and times for connections
> created to my server in last 3 months for example?
> Is there any log file or any option to set that for the future access?
> regards,S
>
>