Database Access

[sacramento]

Hi to all,

I have here a strange problem with the same Database Access in diferent computers.


The problem is:
In one computer i have a field, that are defined to String and the values in this fiels are:
(This computer have Office XP)
24.52
30.85
45.85
46.96

Ok...I had install in another computer with Office 2017 the same database and the values are:
2452
3085
4585
4696

The field are defined in both database with string but the values are different.

Someone can tell why this happen?


Thanks

[jmcilhinney]

Your description is a bit vague. Please provide a FULL and CLEAR explanation of the actual column configuration. Also, how exactly ware you viewing this data? Is it through Access itself or another application? While it shouldn't matter if the data is text and not actual numbers, have you checked that the culture settings are the same on both machines?

That last point raises the question, why are you storing apparently numeric data as text?

[sacramento]

Hi,

Also, how exactly ware you viewing this data?
Answear - i see it like i had describe


Is it through Access itself or another application?
Answear - through App vb.net


While it shouldn't matter if the data is text and not actual numbers, have you checked that the culture settings are the same on both machines?
Answear - Yes...settings are the same in both machines

That last point raises the question, why are you storing apparently numeric data as text?
Answear - You see numbers but for this APP they are not treat like numbers


Thanks

[jmcilhinney]

Please provide a FULL and CLEAR explanation of the actual column configuration.

You still haven't provided that.

Also, how exactly ware you viewing this data?
Answear - i see it like i had describe

My question was not what you were seeing but where (not "ware", sorry) you were seeing it.

Is it through Access itself or another application?
Answear - through App vb.net

Yet you didn't mention that and you haven't shown us any VB.NET code. You seem to be making an assumption that your application couldn't be to blame but if you don't know what is to blame then that's a bold assumption.

[sacramento]

Hi,

Sory the delay of the answear.

You still haven't provided that.
The configuration of the column is:




My question was not what you were seeing but where (not "ware", sorry) you were seeing it.
Answear: Wha't I'm seeing is in one PC, that the configuration is correct:



An this is in the other PC that change the configuration in the column:






Yet you didn't mention that and you haven't shown us any VB.NET code. You seem to be making an assumption that your application couldn't be to blame but if you don't know what is to blame then that's a bold assumption.

The code:

Private Sub listar_prova_report_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load


     
         con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & My.Application.Info.DirectoryPath & "\slot.mdb"
        con.Open()


        ds = New DataSet
        Dim adapter As OleDb.OleDbDataAdapter

        Dim dt As New DataTable



       
        sql = _
              "select * " & _
             " from " & tabela_abrir_classificacoes & "" & _
             " where prova = '" & listar_prova_opcao & "'" & _
             " and classe  = '" & listar_classe_opcao & "'" & _
             " and data_simples  =  '" & listar_data_opcao & "'"




        adapter = New OleDb.OleDbDataAdapter(sql, con)
        adapter.Fill(dt)
        con.Close()


        For Each r As DataRow In dt.Rows
            With dt.Rows

                'Soma o total das calhas, por piloto, e subtrai as voltas de penalização

                nome_piloto_var = r.Item("nome_piloto")
                carro_piloto_var = r.Item("carro_piloto")
                classe_var = r.Item("classe")
                prova_var = r.Item("prova")
                pista_var = r.Item("pista")
                data_var = r.Item("data")


                voltas_penalizacao_var = r.Item("voltas_penalizacao")

                laranja_total_voltas_var = r.Item("laranja_total_voltas")
                laranja_metros_var = r.Item("laranja_metros")
                laranja_melhor_volta_var = r.Item("laranja_Melhor_tempo_volta")
                total_voltas_e_metros_laranja = (laranja_total_voltas_var + "." + laranja_metros_var)

                azul_total_voltas_var = r.Item("azul_total_voltas")
                azul_metros_var = r.Item("azul_metros")
                azul_melhor_volta_var = r.Item("azul_Melhor_tempo_volta")
                total_voltas_e_metros_azul = (azul_total_voltas_var + "." + azul_metros_var)

                amarela_total_voltas_var = r.Item("amarela_total_voltas")
                amarela_metros_var = r.Item("amarela_metros")
                amarela_melhor_volta_var = r.Item("amarela_Melhor_tempo_volta")
                total_voltas_e_metros_amarela = (amarela_total_voltas_var + "." + amarela_metros_var)

                verde_total_voltas_var = r.Item("verde_total_voltas")
                verde_metros_var = r.Item("verde_metros")
                verde_melhor_volta_var = r.Item("verde_Melhor_tempo_volta")
                total_voltas_e_metros_verde = (verde_total_voltas_var + "." + verde_metros_var)

                vermelha_total_voltas_var = r.Item("vermelha_total_voltas")
                vermelha_metros_var = r.Item("vermelha_metros")
                vermelha_melhor_volta_var = r.Item("vermelha_Melhor_tempo_volta")
                total_voltas_e_metros_vermelha = (vermelha_total_voltas_var + "." + vermelha_metros_var)

                branca_total_voltas_var = r.Item("branca_total_voltas")
                branca_metros_var = r.Item("branca_metros")
                branca_melhor_volta_var = r.Item("branca_Melhor_tempo_volta")
                total_voltas_e_metros_branca = (branca_total_voltas_var + "." + branca_metros_var)


                total_voltas_geral_piloto = total_voltas_e_metros_laranja + total_voltas_e_metros_azul + total_voltas_e_metros_amarela + total_voltas_e_metros_verde + total_voltas_e_metros_vermelha + total_voltas_e_metros_branca
                total_voltas_geral_piloto = total_voltas_geral_piloto - voltas_penalizacao_var

            End With
           
            copiarmovimentos()

        Next

       
    End Sub

    Private Sub copiarmovimentos()

        'Vai gravar na tabela_Resultados o valor na relativo a cada piloto
        'Esta é uma tabela dinâmica que se elimina todos os elementos antes de se introduzir novos registos

        'Dim con1 As New OleDb.OleDbConnection(String.Format("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & drive & IDempresa))
        con.ConnectionString = "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = " & My.Application.Info.DirectoryPath & "\slot.mdb"
        Dim sSQL As String
        Dim contarregisto As Integer = 0

        Dim olecommand As New OleDb.OleDbCommand("select count(*) from tabela_resultados", con)
        olecommand.Connection.Open()
        contarregisto = olecommand.ExecuteScalar() + 1
        con.Close()


        Dim idvar As Integer = contarregisto


        sSQL = "INSERT INTO " & eventos & " (ID, nome_piloto, carro_piloto, classe, pontos, total_voltas_metros, "
        sSQL = sSQL & "prova, pista, data, voltas_penalizacao, laranja_total_voltas, laranja_metros, Melhor_volta_laranja, "
        sSQL = sSQL & "azul_total_voltas, azul_metros, Melhor_volta_azul, amarela_total_voltas, amarela_metros, Melhor_volta_amarela, verde_total_voltas, "
        sSQL = sSQL & "verde_metros, Melhor_volta_verde, vermelha_total_voltas, vermelha_metros, Melhor_volta_vermelha, branca_total_voltas, branca_metros, Melhor_volta_branca ) "

        sSQL = sSQL & "VALUES (@idvar, @nome_piloto_var,  @carro_piloto_var, @classe_var, @pontos_var, @total_voltas_geral_piloto,  "
        sSQL = sSQL & "@prova_var, @pista_var, @data_var, @voltas_penalizacao, @laranja_total_voltas_var, "
        sSQL = sSQL & "@laranja_metros_var, @laranja_melhor_volta_var, @azul_total_voltas_var, @azul_metros_var, @azul_melhor_volta_var, @amarela_total_voltas_var, @amarela_metros_var, @amarela_melhor_volta_var, "
        sSQL = sSQL & "@verde_total_voltas_var, @verde_metros_var, @verde_melhor_volta_var, @vermelha_total_voltas_var, @vermelha_metros_var, @vermelha_melhor_volta_var, @branca_total_voltas_var, @branca_metros_var, @branca_melhor_volta_var )"



        Dim command As New OleDb.OleDbCommand(sSQL, con)
        command.Parameters.AddWithValue("@id", idvar)
        command.Parameters.AddWithValue("@nome_piloto", nome_piloto_var)
        command.Parameters.AddWithValue("@carro_piloto", carro_piloto_var)
        command.Parameters.AddWithValue("@classe", classe_var)
        command.Parameters.AddWithValue("@pontos", pontos_var)
        command.Parameters.AddWithValue("@total_voltas_metros", total_voltas_geral_piloto)
        command.Parameters.AddWithValue("@prova", prova_var)
        command.Parameters.AddWithValue("@pista", pista_var)
        command.Parameters.AddWithValue("@data", data_var)
        command.Parameters.AddWithValue("@voltas_penalizacao", voltas_penalizacao_var)
        command.Parameters.AddWithValue("@laranja_total_voltas", total_voltas_e_metros_laranja)
        command.Parameters.AddWithValue("@laranja_metros", laranja_metros_var)
        command.Parameters.AddWithValue("@Melhor_volta_laranja", laranja_melhor_volta_var)
        command.Parameters.AddWithValue("@azul_total_voltas", total_voltas_e_metros_azul)
        command.Parameters.AddWithValue("@azul_metros", azul_metros_var)
        command.Parameters.AddWithValue("@Melhor_volta_azul", azul_melhor_volta_var)
        command.Parameters.AddWithValue("@amarela_total_voltas", total_voltas_e_metros_amarela)
        command.Parameters.AddWithValue("@amarela_metros", amarela_metros_var)
        command.Parameters.AddWithValue("@Melhor_volta_amarela", amarela_melhor_volta_var)
        command.Parameters.AddWithValue("@verde_total_voltas", total_voltas_e_metros_verde)
        command.Parameters.AddWithValue("@verde_metros", verde_metros_var)
        command.Parameters.AddWithValue("@Melhor_volta_verde", verde_melhor_volta_var)
        command.Parameters.AddWithValue("@vermelha_total_voltas", total_voltas_e_metros_vermelha)
        command.Parameters.AddWithValue("@vermelha_metros", vermelha_metros_var)
        command.Parameters.AddWithValue("@Melhor_volta_vermelha", vermelha_melhor_volta_var)
        command.Parameters.AddWithValue("@branca_total_voltas", total_voltas_e_metros_branca)
        command.Parameters.AddWithValue("@branca_metros", branca_metros_var)
        command.Parameters.AddWithValue("@Melhor_volta_branca", branca_melhor_volta_var)


        con.Open()
        command.ExecuteNonQuery()
        con.Close()



    End Sub

Thanks

[FunkyDexter]

It's not a great idea to post screenshots on the forum. They're really hard to read. However, it looks to me like those screenshots are taken directly from access and the data is different in there, is that correct?

And since we're dealing with strings there shouldn't be any issue around numeric representation, localization settings etc. so it seems the data really is different

If so then something must be different in the way that data is initially created. If I'm reading your code correctly (and I may not be because I'm struggling with the Spanish) you're loading data from a table whose name is held in the tabela_abrir_classificacoes variable, then you're inserting that into a table who's name is held in the eventos variable.

If I've understood everthing correctly then my questions would be:-
1. What is the value of tabela_abrir_classificacoes?
2. Is it the same on both pcs?
3. What is the value of eventos?
4. Is it the same on both pcs?
5. Does the data differ in the tabela_abrir_classificacoes or the eventos table or both?
6. How was the tabela_abrir_classificacoes table initially populated?

[sacramento]

Hi,

Yes the screenshots are taken directly from access, and like i had say in the first post, this is the problem...in one PC the database present the values correctly, and in other PC the values are in a wrong format.

1. The value is "Tabela_classificacoes_2018"...this is the table that i have the elements
2. Yes
3. Eventos = "Provas_2018"....annother table with elements
4. Yes
5. Yes difer in both tables
6. Sory the screenshot, but is the only way i have to show:



Thanks

[jmcilhinney]

6. Sory the screenshot, but is the only way i have to show:

Actually, you could have just answered my question:

Also, how exactly ware you viewing this data? Is it through Access itself or another application?

That should have been "were" rather than "ware" but you could and should have just said "I'm viewing the data through Access". The Access application displaying the same data from the same database differently is an issue with Access, which is potentially very different to an issue with code that you have written yourself and, for us to help, requires very different information.

[FunkyDexter]

OK, given that you're viewing the data directly and it's string data (it is definitely string data isn't it? I can't read it in the screenshot) then the data is actually different in the two copies of the database.

From your code and your answer to question 1, the earliest source of the data is the table Tabela_classificacoes_2018 but your code does not populate that - it assumes it is already populated.

So what populated Tabela_classificacoes_2018?

[cubsm22p]

change both database data type to number then put field size to double that will make all your decimals show

[Ecniv]

What I think the helpful people are trying to say is that your vb .net application creates an access mdb (or latest version) which is not the same location. ie on one computer it is on the c drive. On the other it is on a c drive. So in essense you have two dbs, hence the data being different.


If it is the same mdb file on a network shared location, the only other thing I can think of is that the one computer has some custom formatting set on the mdb.




Now for my bad portuguese as that is what I think your comments in the code are...
Para cada aplicação o arquivo do ms access (mdb ou mais novo) é separado. Porque desse, os dados na string são diferente.
Se o arquivo é mesmo, locado na rede compartilhado, só outro coisa eu consigo pensar é um computador/ms access tem uma customizado formato pra campo.