Showing posts with label program. Show all posts
Showing posts with label program. Show all posts

Tuesday, March 27, 2012

Direct access of MDF file?

I am a project manager with a contractor that is very protective of
his programmer.
I've just received an alpha version of a program that appears to
access the .mdf file directly, maybe without a connection string. I
have not seen the source code yet.
I can picture a half dozen reasons not to do this mostly related to
server directory permissions, IT ownership, etc.
Is there any reason to do it? I'd like to do my homework before
telling the contractor to do what I tell him regardless of the cost.
Thanks in advance,
DennisWhat do you mean, "access the MDF file directly"? Why do you think the
program does this, if you haven't even seen the source code?
--
http://www.aspfaq.com/
(Reverse address to reply.)
"Hussman" <hussman_30@.hotmail.com> wrote in message
news:9c4bd326.0411160813.6865b58a@.posting.google.com...
> I am a project manager with a contractor that is very protective of
> his programmer.
> I've just received an alpha version of a program that appears to
> access the .mdf file directly, maybe without a connection string. I
> have not seen the source code yet.
> I can picture a half dozen reasons not to do this mostly related to
> server directory permissions, IT ownership, etc.
> Is there any reason to do it? I'd like to do my homework before
> telling the contractor to do what I tell him regardless of the cost.
> Thanks in advance,
> Dennis|||Not sure what you mean by direct access to an mdf file - the
only thing I can think of would be something possibly using
sp_attach_db, sp_detach_db.
-Sue
On 16 Nov 2004 08:13:20 -0800, hussman_30@.hotmail.com
(Hussman) wrote:
>I am a project manager with a contractor that is very protective of
>his programmer.
>I've just received an alpha version of a program that appears to
>access the .mdf file directly, maybe without a connection string. I
>have not seen the source code yet.
>I can picture a half dozen reasons not to do this mostly related to
>server directory permissions, IT ownership, etc.
>Is there any reason to do it? I'd like to do my homework before
>telling the contractor to do what I tell him regardless of the cost.
>Thanks in advance,
>Dennis|||Also, there's an option in the ODBC DSN for filenames to attach. Perhaps such an option also exists
in an ADO(.NET) connection string and this is used?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:alakp0hh4da9ga6dkoskrbt3okbd5s0ec4@.4ax.com...
> Not sure what you mean by direct access to an mdf file - the
> only thing I can think of would be something possibly using
> sp_attach_db, sp_detach_db.
> -Sue
> On 16 Nov 2004 08:13:20 -0800, hussman_30@.hotmail.com
> (Hussman) wrote:
> >I am a project manager with a contractor that is very protective of
> >his programmer.
> >
> >I've just received an alpha version of a program that appears to
> >access the .mdf file directly, maybe without a connection string. I
> >have not seen the source code yet.
> >
> >I can picture a half dozen reasons not to do this mostly related to
> >server directory permissions, IT ownership, etc.
> >
> >Is there any reason to do it? I'd like to do my homework before
> >telling the contractor to do what I tell him regardless of the cost.
> >
> >Thanks in advance,
> >Dennis
>

Wednesday, March 21, 2012

difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.

difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.sql

difficult SQL select statement

Hi all,

i have a table containing 24 columns, i would like to generate a new table by input the program gets from the user concurning of the columns he would like to see.

sound like an easy "select ? from table", but the thing is that how can the function know how to get a different number of variables, i mean, one time the user will want to see one column, and afterwards he will want to see 10 columns, is there a solution except generating 24 functions?.

i looked in all kinds of SQL tutorials and nothing came up so i came here, tnx for your help!.

Alon.

public string GenerateSqlSelect(string[] columnNames){

return "SELECT "+String.Join(", ",columnNames)+" FROM MyTable";

}

|||You could use params keyword to specify that your function has a variable number of arguments.
|||How user will know what are the columns from the table. Does he will write them, or select them using check boxes or some other method. I realy don't understand what are you for. Can you describe how user interface will look like, or some scenario of application work.
It is common practice that you always get all columns from table, but user can control which column want to see, for example in a grid or listview. So he will at first see all columns but it will be able to remove some of them. That configuration can be saved and used for next application start.

Saturday, February 25, 2012

different mdac, different sql server behaviour

I have done a partial conversion of a large MS Access batch processing
program to use a SQL server 2000 back end.
All I have done is move all the access tables in to SQL server and
give them primary keys so that tje Access front end can attach to them
as updateable tables. No pass thru queries or anything that will take
advantage of the SQL facilities.
In my development environment I am running Access 97 on Windows 98.
The client is running Access 97 on Windows XP
I am seeing different behaviour between my environment and the clients
environment - with exactly the same data sitting on the back end SQL
databases and exactly the same Access 97 front end.
I have managed to knock all the bugs out of the program when it runs
in my environment - but in their environment we are still seeing the
dreaded "Record is Deleted" message - seems to occur in some
situations where I use an outer join and attempt to pick up a value
from a field in the non existent record side of the join.
QUESTION - presumably the discrepancies are caused by us running
different versions of MDAC
1) can anyone tell me where I can download that utility that tells you
which version of mdac you are running
2) which part of mdac am I using when access attaches to the SQL back
end but all the work is still being done by the access front end ?
ole db server for Access ? odbc driver for SQL, jet ?
Many thanks
TonyHi
I am not an expert in this but..
> QUESTION - presumably the discrepancies are caused by us running
> different versions of MDAC
> 1) can anyone tell me where I can download that utility that tells you
> which version of mdac you are running
Down load the component check from
http://msdn.microsoft.com/data/mdac/default.aspx
> 2) which part of mdac am I using when access attaches to the SQL back
> end but all the work is still being done by the access front end ?
>
As you are not using passthru queries you may be doing most of the work at
the front end. You will be using the Jet engine to connect to your access
database and (probably) the oledb driver for SQL Server to connect to the
server (unless you are doing it though ODBC).
> ole db server for Access ? odbc driver for SQL, jet ?
> Many thanks
> Tony
John|||(1) MDAC Component Checker
http://www.microsoft.com/downloads/details.aspx?FamilyID=8f0a8df6-4a21-4b43-bf53-14332ef092c9&displaylang=en
(2) I would guess that you are using the ODBC driver, but I don't know. How
did you set up the link to SQL Server in the first place?
--
Keith
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41c9a201.57817078@.news.m.iinet.net.au...
> I have done a partial conversion of a large MS Access batch processing
> program to use a SQL server 2000 back end.
> All I have done is move all the access tables in to SQL server and
> give them primary keys so that tje Access front end can attach to them
> as updateable tables. No pass thru queries or anything that will take
> advantage of the SQL facilities.
> In my development environment I am running Access 97 on Windows 98.
> The client is running Access 97 on Windows XP
> I am seeing different behaviour between my environment and the clients
> environment - with exactly the same data sitting on the back end SQL
> databases and exactly the same Access 97 front end.
> I have managed to knock all the bugs out of the program when it runs
> in my environment - but in their environment we are still seeing the
> dreaded "Record is Deleted" message - seems to occur in some
> situations where I use an outer join and attempt to pick up a value
> from a field in the non existent record side of the join.
> QUESTION - presumably the discrepancies are caused by us running
> different versions of MDAC
> 1) can anyone tell me where I can download that utility that tells you
> which version of mdac you are running
> 2) which part of mdac am I using when access attaches to the SQL back
> end but all the work is still being done by the access front end ?
> ole db server for Access ? odbc driver for SQL, jet ?
> Many thanks
> Tony|||On Wed, 22 Dec 2004 17:24:03 -0000, "John Bell"
<jbellnewsposts@.hotmail.com> wrote:
>> 2) which part of mdac am I using when access attaches to the SQL back
>> end but all the work is still being done by the access front end ?
>You will be using the Jet engine to connect to your access
>database and (probably) the oledb driver for SQL Server to connect to the
>server (unless you are doing it though ODBC).
Here is the code snippet I am using (the SQL case) (many thanks for
previous assistance in this area)
I have never really understood which driver I am using in this code -
but would be nice to know if I am looking at mdac version issues.
Could I ask someone to please eyeball my code and tell me which part
of mdac is being used.
For this routine, in the SQL case, the paramter "AttachPath" holds the
server name and database name (eg
"DATABASE=WFimporterWork;SERVER=SQLTESTSVR2;")
- I am doing a DSNless connection
Many thanks
Tony
Sub AttachATable(AttachPath, TableName, AttachName, strDatabaseType,
varUid As Variant, varPwd As Variant)
Dim MyTableDef As TableDef
Set MyTableDef = currentDb().CreateTableDef()
Dim strConnect As String
Select Case strDatabaseType
Case "SQL"
strConnect = "ODBC;DRIVER={sql server};" & AttachPath
& "TABLE=" & TableName & ";Trusted_Connection=No;"
If IsNull(varUid) = False Then
strConnect = strConnect & "UID=" & varUid & ";"
End If
If IsNull(varPwd) = False Then
strConnect = strConnect & "PWD=" & varPwd & ";"
End If
MyTableDef.Connect = strConnect
MyTableDef.Attributes = dbAttachSavePWD
Case "MDB"
MyTableDef.Connect = ";DATABASE=" + AttachPath
AttachName = AttachName & "_mdb"
End Select
MyTableDef.SourceTableName = TableName
MyTableDef.Name = AttachName
currentDb().TableDefs.Append MyTableDef ' Attach table.
End Sub|||Hi
ODBC is part of MDAC and you are using the SQL Server ODBC driver.
John
<ace join_to ware@.iinet.net.au (Tony Epton)> wrote in message
news:41ca1245.86557359@.news.m.iinet.net.au...
> On Wed, 22 Dec 2004 17:24:03 -0000, "John Bell"
> <jbellnewsposts@.hotmail.com> wrote:
>
>> 2) which part of mdac am I using when access attaches to the SQL back
>> end but all the work is still being done by the access front end ?
>>You will be using the Jet engine to connect to your access
>>database and (probably) the oledb driver for SQL Server to connect to the
>>server (unless you are doing it though ODBC).
> Here is the code snippet I am using (the SQL case) (many thanks for
> previous assistance in this area)
> I have never really understood which driver I am using in this code -
> but would be nice to know if I am looking at mdac version issues.
> Could I ask someone to please eyeball my code and tell me which part
> of mdac is being used.
> For this routine, in the SQL case, the paramter "AttachPath" holds the
> server name and database name (eg
> "DATABASE=WFimporterWork;SERVER=SQLTESTSVR2;")
> - I am doing a DSNless connection
> Many thanks
> Tony
> Sub AttachATable(AttachPath, TableName, AttachName, strDatabaseType,
> varUid As Variant, varPwd As Variant)
> Dim MyTableDef As TableDef
> Set MyTableDef = currentDb().CreateTableDef()
> Dim strConnect As String
> Select Case strDatabaseType
> Case "SQL"
> strConnect = "ODBC;DRIVER={sql server};" & AttachPath
> & "TABLE=" & TableName & ";Trusted_Connection=No;"
> If IsNull(varUid) = False Then
> strConnect = strConnect & "UID=" & varUid & ";"
> End If
> If IsNull(varPwd) = False Then
> strConnect = strConnect & "PWD=" & varPwd & ";"
> End If
> MyTableDef.Connect = strConnect
> MyTableDef.Attributes = dbAttachSavePWD
> Case "MDB"
> MyTableDef.Connect = ";DATABASE=" + AttachPath
> AttachName = AttachName & "_mdb"
> End Select
> MyTableDef.SourceTableName = TableName
> MyTableDef.Name = AttachName
> currentDb().TableDefs.Append MyTableDef ' Attach table.
> End Sub

Friday, February 17, 2012

Different access files DTS to mssql

We use a program that creates a access database for each of our payperiods. Its a pain to get a full history of a person from each one. I want to setup a way I can combine all of the access files into one table in mssql. I have a DTS I created into a .bas file. My plan is to make the vb.net code get a directory of the files, then run a function (the .bas file) on each directory appending the access table to the mssql table. The struckture of the access files are that each file is in its own directory, like below:

\\servername\TA\company\07-25-04\Trans.mdb
\\servername\TA\company\08-08-04\Trans.mdb
\\servername\TA\company\08-22-04\Trans.mdb

The table is called "Transactions" and they are the same in each mdb file in terms of fields. Every two weeks there is a new directory that is added to the company directory. So I can either dump all and append all, or only append new data and only use the dump/append when there is a problem.

I am just not sure how to do that. Any ideas?Since the structure of each of the .MDB files is the same, it sounds to me like a DTS package using a Global Variable would be a good solution. The Global Variable would be used for the name of the Access file to import. Your VB.NET code would loop through the files to process, feeding DTS the name of each file one by one via a Global Variable.

You might consider keep a SQL table of the files you have processed so your VB.NET program can check against that to see if it has already processed and given file.

Terri|||Sounds good....... how do I do that?|||Okay, I have some code in vb.net to get each date from a startdate. I made a DTS and exported it into a VBS file. How doI get that code to run in my VB.net solution? The vbs file has amain() and some other subs. Just not sure how to referenceit. (I can post the vbs script if needed)