Dim cn As New ADODB.Connection
Dim rs_sub_total As New ADODB.Recordset
Dim Num As String
cn.Open 省略
rs_sub_total.CursorLocation = adUseClient
rs_sub_total.Open "TRANSFORM SUM(請求単価) AS クロス請求金額 " & _
"SELECT 消費税率, SUM(請求単価) AS 請求金額 " & _
"FROM tbl請求合算Sub " & _
"WHERE 合算請求書番号 = '" & Num & "' AND 消費税率 IS NOT NULL " & _
"GROUP BY 消費税率 " & _
"ORDER BY 消費税率 " & _
"PIVOT 合算請求書特殊行;", _
cn, adOpenKeyset, adLockOptimistic
Function MyBase(数値 As Long, 基数 As Long, 桁数 As Long) As String
Const num = "0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZ"
Dim q As Long '商
Dim r As Long '余り
If 基数 <= 1 Or 基数 > 36 Or 数値 < 0 Then
Exit Function
End If
q = 数値
Do
r = q Mod 基数
q = q \ 基数
MyBase = Mid(num, r + 1, 1) & MyBase
Loop While q > 0
MyBase = Right(String(桁数, "0") & MyBase, 桁数)
End Function
入力フォームの挿入前処理に下記のコードを設定する。
Private Sub Form_BeforeInsert(Cancel As Integer)
Randomize
Dim DNum As String
Do
DNum = Format(Int(Rnd * 99999999), "0000-0000-") & MyBase(Rnd * 1679615, 36, 4)
Loop Until IsNull(DLookup("伝票番号", "テーブル1", "伝票番号='" & DNum & "'"))
Me.伝票番号 = DNum
End Sub
"#" & Date & "#"だと文字列になります。日付/時刻型フィールドに文字列を入力しようとしているのでデータ型変換エラーになります。
Date関数の戻り値はDate型(日付/時刻型)なのでそのまま代入すればエラーになりません。
daoRs!年月日 = Dateそのランダムな数値は他のレコードと重複しないようにする必要はありますか。
おそらく必要性があると思いますので、その場合は、
入力フォームの挿入前処理に下記のコードを設定すればいいでしょう。
フィールド名、テーブル名は実際のものに変更してください。
重複してもいいのなら、ループさせる必要はないです。
フィールドの枠線とは、テキストボックスなどの境界線のことでしょうか。
ExcelとACCESSでは画面や印刷時の線の描画法が異なるようなので微妙に異なると思います。
ワードなどのワープロとも異なるように気がします。
Excelなどは画面と印刷時の結果が異なるということもよくあるみたいですし、
Accessもプリンターによって線の太さが異なるということもよくあります。
実際に印刷してみて微調整するという作業はどうしても必要になりますね。
自分も長年の悩みですが、解決できていません。
細かいサイズ調整や位置合わせは「拡大鏡」を使いますが、使いやすいとはいえないですね。
大きいディスプレイに買い換えるとかの物理的な解決法しかいまのところ思いつきません。
hatena様
すずやん様
出来ました！
Accessリンクテーブル参照のADO接続で出来ました。
やはりSQL Server参照のADO接続ではできませんでした。
大変ありがとうございました。
すずやん様
調べていただき、ありがとうございます。
SQL Serverでは出来ないのですかね。
ADOでSQL Serverを直接見に行っているので、それをAccessのリンクテーブルを見に行くように変更すればできるか試してみます。
ちなみに、ご提示いただいたリンク先のコード記述をVBAで出来るのでしょうか？
クエリのSQLビューにて確認しました。
SELECT文のところで、「SUM(請求単価) AS 請求金額」が不要だったので削除しましたが、同じエラーが出ます。
cnの省略も省略せずに載せます。
エラーメッセージは、
実行時エラー '-2147217900(80040e14)':
'請求単価' 付近に不適切な構文があります。
合算請求書番号のデータ型は「短いテキスト」です。SQLサーバーリンクで、サーバーの方の型は「nvarchar(255)」です。
よろしくお願いします。
たぶんですが、SQL Serevrでは「TRANSFORM」を使ったクロス集計ができないと思われます（間違っていたらすみません）
ネットで検索してもSQL Serevrを使用した例が出てきません。
SQL Serevrの場合は下記のような手法で行うのではないでしょうか。
https://style.potepan.com/articles/25072.html
まずは、
クエリのデザインビューでご希望の結果になるものを作成して、SQLビューにして、
それとVBAに記述したSQL文に違いがないか確認してみてください。
それで解決しない場合は、
コード文はマークダウン記法のコードブロック内にいれてください。
詳細は下記のページを参照ください。
Microsoft Access 掲示板 の使い方 Microsoft Access 掲示板 - zawazawa
コードをコードブロック内に入れたものを投稿してください。
そのエラーメッセージを正確に提示してください。
「合算請求書番号」フィールドのデータ型も提示してください。
コードを貼り付けさせていただきました。
ただ、"FROM tbl請求合算Sub " & を
"FROM tbl請求＿合算Sub " & に変更しました。（＿は実際は半角です。なぜか投稿するとなくなってしまうので全角にしました。）
それでもエラーが出てしまいます。
ADOではクロス集計は出来ないのでしょうか？
hatena様
再度、ありがとうございます。
「 _」は全箇所に既に入っております。なぜかサイトの投稿では消えてしまっています。
それと、フィールド名を「クロス請求金額」、「請求金額」にも変更しましたが、同じエラーが出てしまいます。
構文エラーですね。
コードを下記に修正してみてください。
１つのコマンド文を改行して記述するときは、行末に 「 _」 (半角スペース+アンダーバー)が必要です。
あと、変数名も途中から間違ってますね。
すずやん様
hatena様
ありがとうございます。
試してみましたが、
SELECT下の「SUM(請求単価) AS 請求金額」を削除しましたが、ダメでした。
一方のフィールド名を変えてみましたが、ダメでした。
1行目のクロス集計と
TRANSFORM SUM(請求単価) AS 請求金額
2行目の行集計
SUM(請求単価) AS 請求金額
のフィールド名が重複しているのがエラーの原因でしょう。
どちらかフィールド名を変更すればいいでしょう。
実行できないのでなんともいえませんが、下記リンク先の内容から考えるに、SELECT下の「SUM(請求単価) AS 請求金額」必要ないのかな、と思えます。
https://qiita.com/fuk101/items/731379d12cd7f5559fb2
＞配置したグラフの表示部分を囲む点線の枠
「最背面」にすると実行時、完全にグラフが表示されたあとに消えます。テーブルを経由しているので、表示までに少しタイムラグがあるようです。
クエリを直に入れられれば改善できそうではあります。
とりあえずエクセルをインポートするVBAを組んではいかがでしょうか。
流れ的には以下の感じかと思います。概略です。
1.取り込みボタンなどでVBAを呼び出す。
2.エクセルをインポートするVBA（ループ形式）で、取り込み対象のExcelの1行を読む。ループ中では各項目を別の変数に一旦保存
3.保存した変数から、キーとなる項目を判別、結果から受注データを登録（または既存の受注データと紐つけるかも判別）
4.登録した受注データのキーIDと一緒に変数の内容を明細テーブルに書き込む
5.ループで全行をまわす
ACCESSで直接入力できているなら、キーの割り振りのルールは決まっていると思いますので、それをインポートのループを使ってVBA内でやるイメージかと思います。
回答有り難うございます
具体的に書きますと、
ACCESSでは受注(伝票)テーブルとその明細が別テーブルになっていて1対多の構造で入力されています
ACCESSで直接入力する分には問題ないのですが
Excelのデータをインポートする必要があります
Excelでは、伝票の概念がなく、ACCESSの明細にあたる部分のみが入力されています
インポートするときに、日付と取引先でまとめて1伝票とし、伝票テーブルに伝票を作成し、1対多の関係にしたいです
丁・番地・号を区切る-（ハイフン）に、別の似たような横棒の記号が使われている
この横棒の記号をハイフンに変換したい
というだけなら、上記の私の回答の方法で抽出して手作業でなんとかなるでしょう。(データ量にもよりますが)
ただ、無規則に入力されたデータは、実際はそれだけでは済まない場合がおおいでしょう。
厳密に住所データの正規化をしようとするとなかなか困難です。
Excel上で利用できる下記のようなWebサービスがありますので、それを使うのも検討されるといいかもしれません。
住所の統一 ★ | ExcelAPI
住所の分割 ★ | ExcelAPI
住所に使われる文字は郵便局のデータベースで確認することができます。ここにアラビア数字を含めれば住所に使われている文字を作ることができます。これだけで一仕事ですが
ここに含まれていない文字があれば、「住所に使われるはずのない文字」となりますが、表記ゆれを考えれば、「リストにはない文字を使っているが正しい住所」のデータがあるかもしれません
文字一覧とのデータチェックもかなり大変です
雑に、楽にやるなら、「日本語」「アラビア数字」「-（ハイフン）」以外の文字ととらえて一括変換してしまえばそれっぽくなると思います
VBAで文字列から日本語を抽出する(Excel作業をVBAで効率化さん)
リンク先の例では「日本語だけを残す（日本語以外の文字を消す（空白に置き換える））」としているので、これにアラビア数字も含めて、置き換え文字を「-（ハイフン）」にしてやればそれっぽくなるんじゃないでしょうか
リファレンスを読んだ限りでは、DAOには接続先DBのトランザクション分離レベルを確認するためのプロパティまたはメソッドは存在しないように見えます。
Accessデータベースエンジン以外への外部DBへの接続にはADOが推奨されていますが、
どうしてもDAOでAccess以外のDBに接続する必要があり、かつ事前にトランザクション分離レベルを知ることができない場合は
外部DB側にトランザクション分離レベルを返却するストアドプロシージャを用意してDAOで呼び出すなどの工夫が必要になるのではないでしょうか。
・Workspace.IsolateODBCTransについて
通常、同じDBへ接続する複数のWorkspaceのODBC接続は共有されますが、Workspace.IsolateODBCTransをTrueにすることで、Workspaceごとに個別のODBC接続を確立することができます。接続先DBがひとつの接続に対し複数のトランザクションの同時実行を許可しない場合は、複数のWorkspaceを用意しこのプロパティをTrueにすることでトランザクションの同時実行が可能になります。
<参考>
Workspace.IsolateODBCTrans プロパティ (DAO)
https://learn.microsoft.com/ja-jp/office/client-developer/access/desktop-database-reference/workspace-isolateodbctrans-property-dao
ーーーー以下は上記リンク先より引用ーーーー
注釈
状況によっては、同じ ODBC 接続上で同時に発生する複数のトランザクションを保留状態にする必要があります。 これを行うには、それぞれのトランザクションに対して別々の Workspace オブジェクトを開く必要があります。 各 Workspace オブジェクトがデータベースに対して独自の ODBC 接続を確立できますが、これによりシステムのパフォーマンスが低下します。 通常、トランザクションの分離は不要であるため、同じユーザーが開いた複数の Workspace オブジェクトからの ODBC 接続は、既定で共有されます。
Microsoft SQL Server などの一部の ODBC サーバーでは、単一の接続における複数のトランザクションの同時発生は許可されていません。 このようなデータベースに対して同時に複数のトランザクションを保留状態にする必要がある場合は、各 Workspace オブジェクトを開いた直後に、その IsolateODBCTrans プロパティを True に設定します。 これにより、各 Workspace オブジェクトの ODBC 接続が別々に確立されます。
ずずやんさんの言われるように最終的には目視による確認後、手作業での修正になるとは思いますが、
丁・番地・号を区切る記号ということなので、その記号の前後は数字であるということだと思います。
とりあえず下記のような抽出条件で要修正のデーターをある程度絞り込めると思います。
[]内にはハイフンの代わりに使われるような横棒の文字をいれてください。(とりあえず思いついた３文字をいれてます)
これで、前後に数字のある横棒を含むレコードが抽出されます。
わたしが同じ作業をすることになった場合、まず目視で確認するツールを作ると思います。
もし、もとのデータが1万件あったとしても、当てはまるのは数百件ではないでしょうか。
クエリで「-」「一」「－」など「ハイフン」の代わりに使われそうな文字を条件として全て当てはめたクエリを書き、そういった住所が含まれるデータを全て洗い出します。
結果が一覧として表示されるので、目視で確認にながら修正対象のデータを抽出します。
最後に対象となったデータを手動で修正します。
データシートで修正すれば素早く修正できるのではないでしょうか。
住所フィールドは1・2に分かれており、住所1は丁・番地・号まで、住所2は建物名を入力します。
回答で提示して頂いた、住所に使われるはずのない文字はすべてハイフンにするという方法で変換を行いたいのですが、どのようにすればよいでしょうか。AccessでもExcelでも構いませんのでご教示下さい。
無理ですよ。諦めてください
文字とデータの歴史はまぁ、いちいち語るほどでもないんでしませんが、そういう問題と常に戦ってきた歴史です。対処していないシステムから移行する際には全データ1件1件人の目でチェックしなおすしかありません
「住所録」なので例えば次のようなデータですかね
「ハイフン」の代わりに「漢数字の一」を使っているパターンは絶望的です。正しい住所として「漢数字の一」を使っている場合と区別がつきません
それ以外の「ハイフンの代わりの文字」であれば、文字を一文字ずつチェックして住所に使われるはずのない文字はすべてハイフンにするとできるかもしれません
稀に記号を屋号に入れていたりする例を見るので住所に使われるはずのない文字をうまく設定できるかも何とも言えませんけどね
「カタカナの伸ばし棒」と条件を付けているので上記の例を出していますが、建物名まで含まないような住所であれば、使われる文字も限定されるのでいくらかマシになるかもしれません
分割されていないのなら、どうやって区切るか？という新しい問題が発生しますが
すぐにでも直面する例を挙げてみましたが、他にもあれこれ出てくると思います。できることはせいぜい「住所に使わるであろう文字のみのデータはチェック済みとする」くらいでしょう。この「文字のチェック」をするならば、VBAで文字列チェックすればやれます
データベースでは正規化されたデータでないと扱いにくいので、正規化されていないデータの場合は、正規化してから利用するというのが原則です。
Excel側で正規化するのが難しいのなら、いったんAccessにそのままインポートして、それをSQLやVBAを駆使して正規化することになります。
具体的に方法は、現状のAccessのテーブル構成と、エクセルのデータ例などを提示してもらえれば、アドバイスがあると思います。
ちょっとなにを解決したいのかが明確でないのでただの予想ですが、「正規化されていないエクセルの内容をAccessにインポートして受注テーブルと結びつけて保存したい」ということでしょうか。
とりあえず全てのエクセルのデータを読み出して、テーブルに収めてはどうでしょうか。
その後、テーブルデータを一つづつ読み込みながら条件判断し、条件によって「ID」とやらを付加し、受注テーブルと結びつける、という感じ、かもしれません。
なにを持って条件判断するかは制作者歯しだいですね。
自己レスです。とりあえず Workspace.IsolateODBCTrans プロパティ (DAO) でいいのだと思っておきます。
取り急ぎ、一旦AccessのテーブルでSQLServerの結果を受け取り、それをグラフで表示させる方法は実現できました。
今のところ希望通り動作しています。
今回の質問とは関係ないのですが、配置したグラフの表示部分を囲む点線の枠がどうしても消えません。厄介です・・・。
hiroton様
いつもお世話になっております。
わかりました。オペレーターが削除できるのが一番わかりやすそうですね。
＞どのようなエラーが起きるのか？および、それぞれに対する対応については今一度検討の上、必要であれば再度質問してください
ありがとうございます。その時はよろしくお願いします。
どのようなエラーが起きるのか？および、それぞれに対する対応については今一度検討の上、必要であれば再度質問してください
根本的なところは
が問題の原因です。自分（ACCESS）がExcelオブジェクトを作りますよーってしてそのままほったらかしになっている状態ですね
正常に動作したときは
しているので問題が起きません。エラーが発生したとき
.Quitしていいかどうかは分からないので、とりあえずとしておけば、質問のような問題は解決します
hiroton様
透明のコマンドボタンを設置して対応しました。
ありがとうございました！
連携というのは、リンクテーブルのことですか？
それともAdoのSQLServerのテーブルの参照のことですか？
お二方、ご丁寧な説明有難うございました。
無事解決しました。
オートナンバー型を0000-0000-ランダムな英数4桁に加工するのは面倒そうなので、
エクセルと同様の方法で生成して重複チェックをして発番する方法を考えてみました。
まずは、BASE関数に相当するものがAccessにはないので、それを自作します。
標準モジュールに下記の関数を作成します。
入力フォームの挿入前処理に下記のコードを設定する。
これで新規レコード入力時に、重複しない伝票番号が発行されます。
それだと伝票番号が重複する可能性がないとは言えませんよね。
セルに設定した式なら、更新するたびに変化するし。
VBAで値に変換して重複チェックしているのでしょうか。
Accessでするなら、
オートナンバー型のフィールドで、新規レコードの値を「ランダム」に設定しておけば、重複しないランダムな値を発番してくれます。
それをつかって、加工すればどうでしょうか。
印刷内容を設定するそれぞれの領域のことをセクションと呼びます。「レコードを表示させるところ」なら詳細セクションですね
厳密には違う動作ですが、続くセクションの一番上に線（高さ0の長方形）を配置すればそれっぽい見た目になります
ただし、ページフッターだけは特別な表示をされるセクションなので、違うセクションを用意する必要があります。基本的にはレポートフッターセクションで設定すれば良いでしょう
連番ですと、相手に売上規模を知られるので
完全にランダムにしたいです
Excelでは
=BASE(RANDBETWEEN (0,9999),10,4)&"- "&BASE (RANDBETWEEN (0,9999),10,4)&"- "&BASE (RANDBETWEEN (0,1679615),36,4)
としていました
伝票番号のために、わざわざそんなものを作らないといけないの？